MySQL Cookbook

Matching Pattern Metacharacters Literally

Problem

You want to perform a pattern match for a literal instance of a character that’s special in patterns.

Solution

Escape the special character with a backslash. Or maybe two.

Discussion

Pattern matching is based on the use of metacharacters that have a special meaning and thus stand for something other than themselves. This means that to match a literal instance of a metacharacter, you must turn off its special meaning somehow. Do this by using a backslash character (\). Assume that a table metachar contains the following rows:

mysql> SELECT c FROM metachar;
+------+
| c    |
+------+
| %    |
| _    |
| .    |
| ^    |
| $    |
| \    |
+------+

A pattern consisting only of either SQL metacharacter matches all the values in the table, not just the metacharacter itself:

mysql> SELECT c, c LIKE '%', c LIKE '_' FROM metachar;
+------+------------+------------+
| c    | c LIKE '%' | c LIKE '_' |
+------+------------+------------+
| %    |          1 |          1 |
| _    |          1 |          1 |
| .    |          1 |          1 |
| ^    |          1 |          1 |
| $    |          1 |          1 |
| \    |          1 |          1 |
+------+------------+------------+

To match a literal instance of a SQL pattern metacharacter, precede it with a backslash:

mysql> SELECT c, c LIKE '\%', c LIKE '\_' FROM metachar;
+------+-------------+-------------+
| c    | c LIKE '\%' | c LIKE '\_' |
+------+-------------+-------------+
| %    |           1 |           0 |
| _    |           0 |           1 |
| .    |           0 |           0 |
| ^    |           0 |           0 |
| $    |           0 |           0 |
| \    |           0 |           0 |
+------+-------------+-------------+

The principle is somewhat similar for matching regular ...