SQL Pocket Guide, 3rd Edition

Regular Expressions

MySQL, Oracle, PostgreSQL, and SQL Server support regular expressions. SQL Server and MySQL support them only for string comparison, whereas PostgreSQL adds support for a regular-expression substring function and Oracle provides support for that and much more.

Regular Expressions: MySQL

In MySQL, you can perform regular-expression pattern matching using the REGEXP predicate in a manner similar to LIKE:

string REGEXP pattern

REGEXP looks for the specified regular expression anywhere in the target string. For example, to search for variant spellings of Fumee Falls:

SELECT u.id, u.name
FROM upfall u
WHERE u.name REGEXP '(Fumee|Fumie|Fumy)';

MySQL’s regular-expression pattern matching is case-insensitive for nonbinary strings. Because MySQL recognizes the backslash (\) as an escape character in string literals, you must use a double backslash (\\) to represent a single backslash in any pattern that you write as a literal.

Table 1-14 lists the regular-expression operators recognized by MySQL.

Table 1-14. MySQL regular-expression operators

Operator	Description
.	Matches any character, including newlines.
^	Matches beginning of string.
$	Matches end of string.
[ . . . ]	Matches any of a set of characters.
[^ . . . ]	Matches any character not in a set.
[[. xx .]]	Matches a collation element.
[: class :]	Specifies a character class within a bracket expression. For example, use [[:digit:]] to match all digits. Valid character classes are: [:alnum:], [:alpha:], [:blank:], [:cntrl:], [:digit:] ...