search text in mySQL

lucianoes

This is a question about mySQL, not PHP. I am asking it here because I like this forum, I always hang around here. My question is:
How do I search for words in a mySQL table? More especifically, how do I search for whole words in a mySQL table? 

If I use:
select * from table where column = 'string'
mySQL will look for columns that contain 'string' and only 'string'.

If I use:
select * from table where (column like "%string%")
mySQL will look for columns that contain 'string' and possibly something else. That does not necessarily mean that 'string' is a whole word.

I know I could submit the resulting rows to a lot of PHP stunts so that the script displays nothing but the rows where 'string' is indeed a whole word, but in this particular script I would like to avoid that. It doesn't make sense writing additional code for something that mySQL probably can do on its own. I've been reading mySQL documentation for over an hour looking for that but can't seem to find it.
So, if someone can tell me right away what syntax I should use to search for whole words in a database, that would be super.

Thank you very much,
    Luciano ES
    Santos, SP - Brasil

Anon

You could use a like statement that looks like

"WHERE column LIKE " %string% ", which will find the whole word. Unfortunatley, it won't find it if the whole word is at the beginning or end of the string 🙁 so you would have to include "string %" and " string%" to find all the entries with the whole word. This makes the line a bit big: -

WHERE column LIKE "% string %" OR column LIKE "string %" OR column LIKE "% string".

Regular expressions are probably the way to go, but I'm not very adept at those, the best I cam up with was:

WHERE column regexp "^string | string | string$"

Hopefully someone else can come up with a better regular expression than that. Suprisingly (or at least I was suprised), I found that the "like" method is faster on my data than the "regexp" way!

HTH

glj

lucianoes

"^string | string | string$" is not a very simple and good way out. It will make do for now. Thanks.

    Luciano ES
    Santos, SP - Brasil