MySQL regexp word boundary explained

December 22nd, 2011 by Alex Leave a reply »

The word boundary regexp is something you may need when a list of data is saved in a single column. Example:

id   numbers
1    2,34,647
2    78,102,342
3    34,78,103

Now our task would be to select IDs where numbers contain “2” and “34”. One would say that we can do

SELECT * FROM mytable WHERE numbers LIKE "2" OR numbers LIKE "34"

But that won’t work correctly, because 2 will match 2, 102 and 342, which is something we don’t need. In this case MySQL word boundary regex comes handy:

SELECT * FROM mytable WHERE numbers REGEXP "[[:<:]]2[[:>:]]" OR numbers REGEXP "[[:<:]]34[[:>:]]"

Take a look at [[:<:]] and [[:>:]]. This is all we need here. The word boundary regex makes sure the match we are looking for is a standalone word, matching without commas and spaces, and making sure the “2” word doesn’t have additional letters/numbers on left/right side. This is pretty awesome and saves our day!

Comments are closed.