How to Match Word Boundary using SQL?

In SQL, sometimes, you want to search a string field that contains a keyword, but as a whole word. So for example, if you search for ‘word‘, you do not want ‘wordword‘ to be included in the result. Suppose, we have the following data in the MySQL table test.

mysql> select * from test;
+-------------------+
| name              |
+-------------------+
| word              |
| word ok           |
| ok word           |
| wordword          |
| wordword wordword |
| (word)(word)      |
| ,word,            |
+-------------------+
7 rows in set (0.00 sec)

Incorrect SQL to Match Word Boundary using like wildcards

So, if we use percentage symbol % (wildcards) to match one or a few characters, we can use like “%word%” to match, but this gives unnecessary matches.

mysql> select * from test where name like "%word%";
+-------------------+
| name              |
+-------------------+
| word              |
| word ok           |
| ok word           |
| wordword          |   ---------------> incorrect match
| wordword wordword |   ---------------> incorrect match
| (word)(word)      |
| ,word,            |
+-------------------+
7 rows in set (0.00 sec)

Correct SQL to Match Word Bounary

The correct method is to use the REGEXP and the [[:<:]] and [[:>:]] word-boundary markers:

mysql> select * from test where name regexp '[[:<:]]word[[:>:]]';
+--------------+
| name         |
+--------------+
| word         |
| word ok      |
| ok word      |
| (word)(word) |
| ,word,       |
+--------------+
5 rows in set (0.01 sec)

Note: MySQL 8.0 now forces you to use the \b see this example:

select * from test where name regexp  '\\bword\\b'

You have to escape the backward slash \ in “\b” which defines a word bounary.

Learning SQL

• Teaching Kids Programming - Second Highest Distinct Record using SQL and Python
• Teaching Kids Programming - Most Common SQL keywords (Select, Update, Insert, Delete)
• Teaching Kids Programming - Introduction to SQL and the SELECT
• How to Match Word Boundary using SQL?

–EOF (The Ultimate Computing & Technology Blog) —