MySQL: Whole Word Search
Question by Compi | 2016-06-08 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.
Related Topics
MySQL: Combine full text search with LIKE search for words with 3 letters
Tutorial | 2 Comments
The Askingbox Search
Info | 0 Comments
MySQL: Change minimum word length for full text search
Tip | 1 Comment
How to Replace multiple Texts at the same Time
Tutorial | 0 Comments
Google Tips: Search within documents of a specific file type (PDF, DOC, XLS, TXT, HTM etc)
Info | 0 Comments
MySQL: Regular Expressions in MySQL queries
Info | 0 Comments
Hidden Google Easter Eggs
Info | 1 Comment
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 article on askingbox.com. That’s how it’s done.
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:
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.
2016-06-09 at 22:18