How to Find Exact Word Matches in MySQL Using Regular Expressions?

MySQL exact full text matching

Searching for keywords in a text field is a common task, but sometimes you just want to match exact words. For example, if you search for "rid", you don't want to get results like "arid" but only instances of "a rid".

MySQL’s SQL query provides a solution to this problem. This article takes an in-depth look at ways to find complete word matches in MySQL.

The wonderful use of regular expressions

MySQL’s REGEXP function is crucial for fine-tuning text searches. It allows you to use the word boundary markers [[:<:]] and [[:>:]] to limit matching to complete words. Consider the following query:

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

This query matches rows where the keywords field contains "rid" as a single word. It excludes partial matches like "arid" or "co-rid".

MySQL 8.0.4 and higher

In MySQL 8.0.4 and later, the regular expression engine has been enhanced. Word boundary markers have been updated to the standard b. Therefore, the above query becomes:

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

Remember to escape the backslash character (\) with another backslash () to prevent SQL injection vulnerabilities.

This method provides a convenient and efficient way to find complete word matches in MySQL, regardless of your database size or performance requirements. It enables you to refine your queries and increase the relevance of your search results.

The above is the detailed content of How to Find Exact Word Matches in MySQL Using Regular Expressions?. For more information, please follow other related articles on the PHP Chinese website!