Mysql select on word (plural, singular)

Question

In my Mysql DB I have a list of terms like (With First letters Capital and most of the time plurals)

Hairdressers
Restaurants
Beauty Salons 
Fournitures For Restaurants

On my website I have a searchbar where the user can search for those word (my website is a kind of POI finder). But I'm having problems with my query.

SELECT * FROM TABLE WHERE word = 'userword'

1: If the user enter restaurants, it doesn't work because he put 'r' on lower case. 2: if the user enter restaurant either because because he didn't put it in plural

I have tried with SELECT * FROM TABLE WHERE word like 'userword'

This fix the problem for the Capital leters, but I have no idea how to make the query work when the user enter the word in singular. is the only solution add that word in DB as well ?

Thank you

score 3 · Answer 1 · answered Nov 13 '11 at 10:14

3

Maybe it is better with a regular expression that match also plural name testing only for an additional s at the end of the word :

SELECT * FROM table where word REGEXP '^userword[s]?'

Note that regular expression match in MySQL are case insensitive by default.

answered Nov 13 '11 at 10:14

aleroot

71,077
30
176
213

Hello, Ok that could work for ONE word, but the thing is that i have entries with more than one word (fourniture of restaurants) Can you help write the rexep that can valid this user's entry "fourniture restaurant" whitout "s" ? – Johny19 Nov 13 '11 at 21:55
REGEXP '^fourniture restaurant$' – aleroot Nov 13 '11 at 22:01

score 1 · Answer 2 · edited Apr 23 '15 at 01:03

1

When you use LIKE you can use % to signify wildcards

So:

SELECT * FROM table where word like '%userword%'

Matches:

userword, userwords, superuserwords, ...

But if you use LIKE thus (note the additional s):

SELECT * FROM table where word like '%userwords%'

It does not match:

userword, superuserword, ...

edited Apr 23 '15 at 01:03

dave

11,641
5
47
65

answered Nov 13 '11 at 10:08

Haedrian

4,240
2
32
53

Yes I know, but in my DB i have quit a lot of other words and there is stuff like "Fourniture of Restaurant" "stuff for Restaurants" "Blabla restaurant" and if the user only enter like "restaurant" it could return "furniture of restaurant" – Johny19 Nov 13 '11 at 10:27
So either wildcard just the end (I think a _ is a single character if that helps) - or use the regex that was suggested by another poster. – Haedrian Nov 13 '11 at 10:29

score 1 · Answer 3 · answered Nov 13 '11 at 11:01

1

The best solution is to use a proper search engine for this which will use an appropriate analyser and a stemmer in order to serve quality results back to the users. Such a search engine is lucene and if you don't want to deal with implementation details you can use Solr which is a search server.

answered Nov 13 '11 at 11:01

cherouvim

31,725
15
104
153

Solr and lucene ? Isn't it really complex to use and install knowing that I have only one table with ONE row (1500 words to be precise). I tried to look around mysql FULLTEXT but the only function that I need is not included (stemming) – Johny19 Nov 13 '11 at 19:17
Then you can simply provide both versions of the words (singular, plural) and match against these. – cherouvim Nov 13 '11 at 20:53

Mysql select on word (plural, singular)

3 Answers3