Compare LIKE clause with entire word

Question

Currently, my SQL search query use LIKE clause with %some-word-to-search% wildcards.

The problem of using percent wildcard is that it's returning all the rows which containing a part of my search param, e,g:

If I search car It will also show cartoons to me! is there any way to compare like clause only with entire words instead of part of it?

Any suggestion would be appreciated...

see if this discussion could help http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql — arilia, Dec 31 '13 at 14:53
Do not use wildcard character. You will get exact matching of record. — Saharsh Shah, Dec 31 '13 at 14:53

score 3 · Accepted Answer · answered Dec 31 '13 at 14:55

3

You can use a regular expression that matches word boundary. For example, this will match rows where the text contains "car" as a single word, even when it's the first or last word, or adjacent to punctuation:

SELECT * FROM mytable WHERE mytext REGEXP '[[:<:]]car[[:>:]]';

Note that these kinds of queries require a table scan. A full text index can give better performance.

answered Dec 31 '13 at 14:55

Joni

108,737
14
143
193

Thanks for this excellent way, I will accept this answer in next 2 minutes. – iSun Dec 31 '13 at 15:04

score 0 · Answer 2 · answered Dec 31 '13 at 14:55

0

You can also look at the natural language search function match():

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

answered Dec 31 '13 at 14:55

Kevin Seifert

3,494
1
18
14

Compare LIKE clause with entire word

2 Answers2