Matching Pattern Metacharacters Literally

4.9.1 Problem

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

4.9.2 Solution

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

4.9.3 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 expression metacharacters. For example, each of the following regular expressions matches every row in the table:

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

To match the metacharacters literally, just add a backslash, right? Well, try it:

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

It didn't work, because regular expressions are processed a bit differently than SQL patterns. With REGEXP, you need a double backslash to match a metacharacter literally:

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

Because backslash suppresses the special meaning of metacharacters, backslash itself is special. To match a backslash literally, use double backslashes in SQL patterns or quadruple backslashes in regular expressions:

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

It's even worse trying to figure out how many backslashes to use when you're issuing a query from within a program. It's more than likely that backslashes are also special to your programming language, in which case you'll need to double each one.

Within a character class, use these marks to include literal instances of the following class constructor characters:

Related

Категории