Search for "whole word match" in MySQL

Understanding word boundaries

Word boundaries are spotlights in a text data detangling exercise when you need to grab an exact match without getting tangled in substrings!

Some use-cases could be:

• Looking for exact usernames in a database. No one wants a CaptainJackSparrow21 showing up when searching for CaptainJackSparrow.
• Hunting down search terms in text data. 'Apple' should not spread out to 'Apple pie', 'Apple company', 'Green apple', etc. Juicy, but an unwanted mess!
• Catching keywords in content management systems.

The syntax flavor varies:

• For MySQL versions before 8.0.4 use [[:<:]] and [[:>:]]. These magical symbols denote the start and the end of a word, respectively.

SELECT * FROM your_table WHERE your_column REGEXP '[[:<:]]apple[[:>:]]'; --Don't take my word for it. Take the whole word!


• From MySQL 8.0.4 onwards, \b is the new cool kid, but it needs to be escaped using double backslashes as \\b.

SELECT * FROM your_table WHERE your_column REGEXP '\\bapple\\b'; --Throwback to the good ol'\days – I'm bound to the rules (\\b) again.


For better efficiency, consider doing the search operation in SQL using REGEXP. This keeps a lid on the back-and-forth data travel between the database and the application layer, ensuring a smooth ride!

MySQL REGEXP and updates: what to remember

Regular expressions in MySQL are like wizarding spells. They are versatile but come with a small list of things to remember:

• Use them, and they are case-sensitive by default (like shouting "Expecto Patronum!" vs. whispering it). For a case-insensitive match, incorporate the all-powerful REGEXP_LIKE() function or combine with LOWER().

SELECT * FROM your_table WHERE LOWER(your_column) REGEXP LOWER('\\bapple\\b'); --We all have capitalized on this trick, haven't we? 😉


• Be aware that MySQL updates may bring forth changes in the behavior of regular expressions. Always keep an eye on the documentation or release notes when upgrading your MySQL version. After all, we don’t want to be left behind in any wizarding duel, do we?

Advanced MySQL REGEXP cases

Dealing with special characters

When your search term has the special characters that double up as part of RegEx syntax. Remember to escape them:

SELECT * FROM your_table WHERE your_column REGEXP '\\bco\\-op\\b'; --Remember, we're special, character!


Exploring compound words

For compound words or words with hyphens or other non-alphanumeric characters:

SELECT * FROM your_table WHERE your_column REGEXP '\\be\\-mail\\b'; --E-xactly what you're looking for!


Multiple word variations

To cover multiple variations of a word, such as 'apple' and 'apples':

SELECT * FROM your_table WHERE your_column REGEXP '\\b(apple|apples)\\b'; -- An apple a day keeps the doctor away. But what about apples? 
