3

Possible Duplicate:
MySQL - How to search for exact word match using LIKE?

For example, if Im trying to get rows with the word "ever", I could do this...

SELECT * FROM 'table' WHERE `title` LIKE %ever%

But that would also give me results for titles with the words "forever, however, never".

How can I find only the titles which contain "ever" as its own word?

Community
  • 1
  • 1
Ni Le
  • 201
  • 1
  • 3
  • 7
  • There is already a question with answers to this issue: http://stackoverflow.com/questions/5743177/mysql-how-to-search-for-exact-word-match-using-like – diggersworld Jan 04 '13 at 21:16

2 Answers2

4

Use a REGEXP regular expression to match using word boundaries [[:<:]]word[[:>:]] around your search term:

SELECT * FROM `table` WHERE `title` REGEXP '[[:<:]]ever[[:>:]]'

Note that this is case-insensitive by default. For case sensitivity, you need to match it in BINARY mode:

SELECT * FROM `table` WHERE `title` REGEXP BINARY '[[:<:]]ever[[:>:]]'

Since it cannot make use of indexing, performance can be quite poor on large tables however.

Edit: Sorry I had PCRE boundaries in there, which MySQL doesn't support.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • 3
    I'd note that this'd be nasty on a large table, too. – ceejayoz Jan 04 '13 at 21:18
  • @NiLe If you are satisfied with at least one occurance of `ever` within the string, you may do a [`instr`](http://www.w3resource.com/mysql/string-functions/mysql-instr-function.php) e.g. `select * from table where instr(title,'ever') > 0;` `instr` returns first occurnace of a string within a string. ;) @MichaelBerkowski in terms of performance wise `instr` goes a bit faster than `regex` for a large table. – bonCodigo Jan 05 '13 at 00:12
2

You can use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers: 

SELECT *
FROM table 
WHERE keywords REGEXP '[[:<:]]ever[[:>:]]'
echo_Me
  • 37,078
  • 5
  • 58
  • 78