[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Mysql search query ignoring dots
From:       Barbara Picci <barbara.picci () sardi ! it>
Date:       2011-01-24 13:43:29
Message-ID: p0624080bc96332710890 () [192 ! 168 ! 168 ! 51]
[Download RAW message or body]

Hi all,

I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH AGAINST.
But there is a problem. In this field there are company names that 
contain dots, for istance I've "PO.SE. srl" and I want to find it if 
the user search for: "POSE" or "PO.SE" or "P.O.S.E." etc.
I googled in the web but I don't find any solution. I don't want to 
add a new field with the cleaned version of my string because I would 
like to solve with the query and I prefer that the mysql table not 
become too big. But if I will not find a different solution, I will 
use this escamotage.
I've find a post that is similar but the solution don't seem to solve 
my situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
is indifferent and don't change my results.

My query, searching "POSE", is:

select aziende.* from aziende where 1>0 AND 
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR 
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.', 
'')) order by aziende.ragione_sociale limit 0, 10

The alternative choice could be REGEXP but I've red that it make my 
query slow in a table of millions of records and I don't know how to 
exclude dots in the regular expression.

Can anyone help me?

Thanks in advance.
Barbara

-- 
------------------------
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari  - tel. (+39) 070400240
http://www.microsrl.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]