How Can I Achieve Whole Word Matching in MySQL Searches?

Precise Keyword Searches in MySQL Using Regular Expressions

Improving Search Accuracy with Regular Expressions

For precise whole-word matching in MySQL queries, leverage the REGEXP operator in conjunction with word boundary markers. This ensures your keywords only match when they're independent words, preventing partial matches within larger words.

Older MySQL Versions (Pre-8.0.4):

<code class="language-sql">SELECT *
FROM table 
WHERE keywords REGEXP '[[:<:]]rid[[:>:]]'</code>

The [[:<:]] and [[:>:]] markers define word boundaries.

MySQL 8.0.4 and Later:

MySQL versions 8.0.4 and later use a different regular expression engine. The recommended approach now is to use the standard word boundary marker b:

<code class="language-sql">SELECT *
FROM table 
WHERE keywords REGEXP '\brid\b'</code>

Note the double backslash (\) escaping the backslash character. This is crucial for correct interpretation by the MySQL engine.

The above is the detailed content of How Can I Achieve Whole Word Matching in MySQL Searches?. For more information, please follow other related articles on the PHP Chinese website!