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:

  • To include a literal ] character, list it first.
  • To include a literal - character, list it first or last.
  • To include a literal ^ character, list it somewhere other than as the first character.
  • To include a literal character, double it.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net