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:] ...

Get SQL Pocket Guide, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.