MySQL —

REGEXP Matching Special Characters

The regular expression language is made up of special characters that have specific meanings. You've already seen ., [], |, and -, and there are others, too. Which begs the question, if you needed to match those characters, how would you do so? For example, if you wanted to find values that contain the . character, how would you search for it? Look at this example:

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '.'
ORDER BY vend_name;

The above statement results the following output:

+-----------------+
| vend_name       |
+-----------------+
| Hewlett Packard |
| Apple           |
| Microsoft Inc.  |
+-----------------+

That did not work. . matches any character, and so every row was retrieved.

To match special characters they must be preceded by \\. So, \\- means find and \\. means find .:

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

The above statement results the following output:

+-----------------+
| vend_name       |
+-----------------+
| Microsoft Inc.  |
+-----------------+

That worked. \. matches ., and so only a single row was retrieved. This process is known as escaping, and all characters that have special significance within regular expressions must be escaped this way. This includes ., |, [], and all of the other special characters used thus far.