search keyword in db

[scorpioy]

Im trying to implement a search function in my database but not sure what would be a smart way to do it.

Suppose I have a blog column and want to search if "middle school" is inside, I would do: columnName like '%middle school%' but if the search key words are "middle school soccer", I don't know how to recognize and break this into two logic parts as "middle school" and "soccer". How would this be implemented or is it has to deal with some complext AI concept?

Right now I only know to use columnName like '%middle%' or columnName like '%school%' or columnName like '%soccer%'. Hope someone can give me some advice to improve it. Thanks.

[konsu]

given a search string such as "middle school soccer", do you want to search for the exact string or for any words from this phrase or for all words?

[scorpioy]

"middle school soccer" should be meant to say: "middle school" and "soccer", but I don't know how to seperate them meaningfully.

Plus, is there any way to search for a whole word? I mean when I search 'soccer', I want to see result wich contains word 'soccer', not 'soccerball', nor '???soccer'. How can I do this?

Thanks.

[konsu]

1. i think it is not feasible for many applications to write code that would attempt to grammatically analyse a phrase. most of search engines that i have seen use boolean expressions like ("middle school" and soccer). your code then will need to parse the expression that the user submitted and construct a database query from it.

2. to match a word, you can use regular expressions: http://dev.mysql.com/doc/mysql/en/pattern-matching.html

[scorpioy]

thank you very much, so now i know using ^ and $ to enclose the word for searching a complete word