Search for "whole word match" in MySQL

I would like to write an SQL query that searches for a keyword in a text field, but only if it is a "whole word match" (e.g. when I search for "rid", it should not match "arid", but it should match "a rid".

I am using MySQL.

Fortunately, performance is not critical in this application, and the database size and string size are both comfortably small, but I would prefer to do it in the SQL than in the PHP driving it.

Solution 1:

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

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

Update for 2020: (actually 2018+)

MySQL updated its RegExp-Engine in version 8.0.4, so you will now need to use the "standard" word boundary marker \b:

SELECT *
FROM table 
WHERE keywords REGEXP '\\brid\\b'

Also be aware that you need to escape the backslash by putting a second backslash.

Solution 2:

Found an answer to prevent the classic word boundary [[::<::]] clashing with special characters eg .@#$%^&*

Replace..

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

With this..

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

The latter matches (space, tab, etc) || (comma, bracket etc) || start/end of line. A more 'finished' word boundary match.

Solution 3:

You can use like with the wildcard marker to catch the possibilities (at start, at end, in middle, and alone), something like this should suffice:

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

Solution 4:

Use regexp with word boundaries, but if you want also accent insensitive search, please note that REGEXP is a single-byte operator, so it is Worth nothing to have utf8_general_ci collation, the match will not be accent insensitive.

To have both accent insensitive and whole word match, specify the word written in the same way the (deprecated) PHP function sql_regcase() did.

In fact:

utf8_general_ci allows you to make an equality (WHERE field = value) case and accent insensitive search but it doesn't allow you to specify an entire word match (word boundaries markers not recognized)
LIKE allows you case and accent insensitive search but you have to manually specify all combinations of possible word boundaries charactes (word boundaries markers not recognized)
word boundaries [[:<:]] and [[:>:]] are supported in REGEXP, who is a single byte functions so don't perform accent insensitive search.

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

Used on http://www.nonsolodiete.it

Solution 5:

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

This will handle finding rid where it is preceded or followed by a space, you could extend the approach to take account of .,?! and so on, not elegant but easy.