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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With