Whole word matching with dot characters in MySQL
mysql, regex
Solution
This regex does what you want:
SELECT name
FROM tbl_name
WHERE name REGEXP '([[:blank:][:punct:]]|^)u[.]s[.]([[:punct:][:blank:]]|$)'
This matches `u.s.` when preceeded by:
- a blank (space, tab etc)
- punctuation (comma, bracket etc)
- nothing (ie at start of line)
and followed by:
- a blank (space, tab etc)
- punctuation (comma, bracket etc)
- nothing (ie at end of line)
See an SQLFiddle with edge cases covering above points.
Problem
In MySQL, when searching for a keyword in a text field where only "whole word match" is desired, one could use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers: ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]word[[:>:]]" ``` For example, when we want to find all text fields containing "europe", using ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]europe[[:>:]]" ``` would return "europe map", but not "european union". However, when the target matching words contains "dot characters", like "u.s.", how should I submit a proper query? I tried the following queries but none of them look correct. 1. ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]u.s.[[:>:]]" ``` 2. ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]u[.]s[.][[:>:]]" ``` 3. ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]u\.s\.[[:>:]]" ``` When using double backslash to escape special characters, as suggested by d'alar'cop, it returns empty, even though there are something like "u.s. congress" in the table ``` SELECT name FROM tbl_name WHERE name REGEXP "[[:<:]]u\\.s\\.[[:>:]]" ``` Any suggestion is appreciated!