11.5.2. Regular Expressions

A regular expression is a powerful way of specifying a pattern
for a complex search.

MySQL uses Henry Spencer's implementation of regular
expressions, which is aimed at conformance with POSIX 1003.2.
MySQL uses the extended version to support pattern-matching
operations performed with the
REGEXP operator in SQL statements.

This section summarizes, with examples, the special characters
and constructs that can be used in MySQL for
REGEXP operations. It does not
contain all the details that can be found in Henry Spencer's
regex(7) manual page. That manual page is
included in MySQL source distributions, in the
regex.7 file under the
regex directory. See also
Section 3.3.4.7, “Pattern Matching”.

Performs a pattern match of a string expression
expr against a pattern
pat. The pattern can be an
extended regular expression. The syntax for regular
expressions is discussed in Section 11.5.2, “Regular Expressions”.
Returns 1 if
expr matches
pat; otherwise it returns
0. If either
expr or
pat is NULL,
the result is NULL.
RLIKE is a
synonym for REGEXP, provided
for mSQL compatibility.

The pattern need not be a literal string. For example, it
can be specified as a string expression or table column.

Note

Because MySQL uses the C escape syntax in strings (for
example, “\n” to represent
the newline character), you must double any
“\” that you use in your
REGEXP strings.

REGEXP and
RLIKE use
the current character set when deciding the type of a
character. The default is latin1 (cp1252
West European).

Warning

The REGEXP and
RLIKE
operators work in byte-wise fashion, so they are not
multi-byte safe and may produce unexpected results with
multi-byte character sets. In addition, these operators
compare characters by their byte values and accented
characters may not compare as equal even if a given
collation treats them as equal.

A regular expression describes a set of strings. The simplest
regular expression is one that has no special characters in it.
For example, the regular expression hello
matches hello and nothing else.

Nontrivial regular expressions use certain special constructs so
that they can match more than one string. For example, the
regular expression hello|word matches either
the string hello or the string
word.

As a more complex example, the regular expression
B[an]*s matches any of the strings
Bananas, Baaaaas,
Bs, and any other string starting with a
B, ending with an s, and
containing any number of a or
n characters in between.

A regular expression for the REGEXP
operator may use any of the following special characters and
constructs:

Matches any character that is (or is not, if ^ is used)
either a, b,
c, d or
X. A - character
between two other characters forms a range that matches all
characters from the first character to the second. For
example, [0-9] matches any decimal digit.
To include a literal ] character, it must
immediately follow the opening bracket [.
To include a literal - character, it must
be written first or last. Any character that does not have a
defined special meaning inside a [] pair
matches only itself.

Within a bracket expression (written using
[ and ]), matches the
sequence of characters of that collating element.
characters is either a single character
or a character name like newline. The
following table lists the permissible character names.

The following table shows the permissible character names
and the characters that they match. For characters given as
numeric values, the values are represented in octal.

Within a bracket expression (written using
[ and ]),
[=character_class=] represents an
equivalence class. It matches all characters with the same
collation value, including itself. For example, if
o and (+) are the
members of an equivalence class, [[=o=]],
[[=(+)=]], and [o(+)]
are all synonymous. An equivalence class may not be used as
an endpoint of a range.

[:character_class:]

Within a bracket expression (written using
[ and ]),
[:character_class:] represents a
character class that matches all characters belonging to
that class. The following table lists the standard class
names. These names stand for the character classes defined
in the ctype(3) manual page. A particular
locale may provide other class names. A character class may
not be used as an endpoint of a range.

These markers stand for word boundaries. They match the
beginning and end of words, respectively. A word is a
sequence of word characters that is not preceded by or
followed by word characters. A word character is an
alphanumeric character in the alnum class
or an underscore (_).

To use a literal instance of a special character in a regular
expression, precede it by two backslash (\) characters. The
MySQL parser interprets one of the backslashes, and the regular
expression library interprets the other. For example, to match
the string 1+2 that contains the special
+ character, only the last of the following
regular expressions is the correct one: