Search for the word "whole word" in MySQL

Question

I would like to write an SQL query that searches for a keyword in a text box, but only if it is a “whole word match” (for example, when I search for “rid”, it should not match “arid”, but it should match “get rid” .

I am using MySQL.

Fortunately, performance doesn't matter much in this application, and the database size and row size are small enough, but I'd rather do it in SQL than in the PHP that manages it.

Answer 1

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

 SELECT * FROM table WHERE keywords REGEXP '[[:<:]]rid[[:>:]]' 

Answer 2

An answer was found to prevent the classical border of the word [[::<::]] when faced with special characters, for example: @ # $% ^ & *

Replace ..

 SELECT * FROM table WHERE keywords REGEXP '[[:<:]]rid[[:>:]]' 

With this..

 SELECT * FROM table WHERE keywords REGEXP '([[:blank:][:punct:]]|^)rid([[:blank:][:punct:]]|$)' 

Recent matches (space, tab, etc.) || (comma, parenthesis, etc.) || start / end of line. A more "finished" match of words.

Answer 3

You can use like with a lookup token to catch the possibilities (at the beginning, at the end, in the middle and alone), something like this should be enough:

select blah blah blah where the column is like 'rid%' or the column is like '% rid' or the column is like '% rid%' or column = 'rid'

Answer 4

Use regexp with word boundaries, but if you want accent-free searches as well, note that REGEXP is a single-byte operator, so there is nothing to do with utf8_general_ci sorting, the match will not be insensitive to accent.

To ensure that the match is not sensitive to the accent and the match of the whole word, specify the word written in the same way as the (sql) PHP function sql_regcase ().

In fact:

• utf8_general_ci allows you to search (WHERE field = value) case insensitive and stress, but does not allow you to specify the match of the whole word (word boundary markers are not recognized)

• LIKE allows you to search case-insensitively, but you must manually specify all combinations of possible word boundary characters (word boundary markers are not recognized)

• word boundaries [[: <:]] and [[:>:]] are supported in REGEXP, which is a single-byte function, so it does not perform searches without emphasis.

The solution is to use REGEXP with word boundaries and the word modified as sql_regcase does.

Used at http://www.nonsolodiete.it

Answer 5

 select * from table where Locate('rid ', FieldToSearch) > 0 or Locate(' rid', FieldToSearch) > 0 

This will work with the search, when it is preceded or followed by a space, you can expand the approach to take into account.,?! etc., but not elegant, but easy.

Answer 6

This is the best answer I've come up with so far:

 SELECT * FROM table WHERE keywords REGEXP '^rid[ $]' OR keywords REGEXP ' rid[ $]' 

I would simplify this:

 SELECT * FROM table WHERE keywords REGEXP '[^ ]rid[ $]' 

but [^] has a special meaning of "NOT space", not "beginning of line or space".

How does REGEXP compare with several LIKE conditions? (Not that performance matters in this application.)