0 0 Votes

MySQL: Whole Word Search

Question by Compi | 08/06/2016 at 23:22

I would like to search my MySQL database for whole words. That means, when searching for the keyword "the", I only want to get the records in which there is really occurring "the" as a whole and individual word.

Up to now, my approach was to work with LIKE '%keyword%'. However, in the case quite as mentioned above, I also got hits for "them" or "nevertheless" what I do not want.

Because of that, I gave LIKE ' %keyword% ' a try. The spaces at the beginning and at the end made that this query really only found data records in which my keyword occurred as a whole word. However, it came to problems at another point: records beginning or ending with the keyword was not found anymore, because they do not have a blank character at their start and end.

So, I came to the query SELECT * FROM tab WHERE col LIKE '% keyword %' OR col LIKE 'keyword %' OR col LIKE 'keyword %' OR col = 'keyword'. This leads to quite better results, but there was another problem: it was not working with punctuation. As soon as there was a point or a comma after the keyword, it was not working anymore.

Now I'm at my wits end. Does someone has any idea how I can solve this and how to cover all of this case is? I think, adding all possible combinations with all possible punctuation, would be too much.

ReplyPositiveNegative

Stefan Trost

Show Profile | Message
Avatar
0Best Answer
0 Votes

In your position, I would not work with LIKE. Instead, I recommend using a fulltext index or regular expressions.

If your decision is to work with a regular expression, a search for "keyword" could look like this:

SELECT * FROM tab 
WHERE col REGEXP '[[:<:]]keyword[[:>:]]'

The word boundary markers [[:<:]] and [[:>:]] are standing for the beginning respectively the end of a word. This can be spaces, special characters, punctuation or even the beginning or end of a string.

One disadvantage of REGEXP is the quite bad performance. So, in many cases it is better to use MySQL's fulltext search. For that, first you have to create a fulltext index over the column that you would like to search. After that, you can search for a whole word in the following way, for example:

SELECT * FROM tab
WHERE MATCH (col) AGAINST ('+keyword' IN BOOLEAN MODE);

Among other things, the fulltext search allows including or excluding words from the search using a + or - sign as a prefix for the word. Because of that, we are writing '+keyword' here.

Which variant you prefer of course depends on your purpose. If you carry out your query less often, creating a fulltext index probably makes no sense. However, if you are searching often in this way, I recommend the fulltext search because of the performance advantages.
09/06/2016 at 22:18

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.