Oddthinking Oddthinking - 1 year ago 83
MySQL Question

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.

Answer Source

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

FROM table 
WHERE keywords REGEXP '[[:<:]]rid[[:>:]]'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download