12.5.2. Regular Expressions

expr NOT REGEXP pat, expr NOT RLIKE pat

This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

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 12.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.

REGEXP is not case sensitive, except when used with binary strings.

mysql> SELECT 'Monty!' REGEXP
                    '.*';        -> 1mysql> SELECT 'new*\n*line'
                    REGEXP 'new\\*.\\*line';        -> 1mysql> SELECT
                    'a' REGEXP 'A', 'a' REGEXP BINARY 'A';        -> 1  0mysql> SELECT 'a' REGEXP '^[a-d]';        -> 1

REGEXP and RLIKE use the character set and collations of the arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 10.1.7.5, "Collation of Expressions".

^

Match the beginning of a string.

mysql> SELECT 'fo\nfo' REGEXP
                    '^fo$';                   -> 0mysql> SELECT
                    'fofo' REGEXP '^fo';                      -> 1

$

Match the end of a string.

mysql> SELECT 'fo\no' REGEXP
                    '^fo\no$';                 -> 1mysql> SELECT
                    'fo\no' REGEXP '^fo$';                    -> 0

.

Match any character (including carriage return and newline).

mysql> SELECT 'fofo' REGEXP
                    '^f.*$';                    -> 1mysql> SELECT
                    'fo\r\nfo' REGEXP '^f.*$';                -> 1

a*

Match any sequence of zero or more a characters.

mysql> SELECT 'Ban' REGEXP
                    '^Ba*n';                     -> 1mysql> SELECT
                    'Baaan' REGEXP '^Ba*n';                   -> 1mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1

a+

Match any sequence of one or more a characters.

mysql> SELECT 'Ban' REGEXP
                    '^Ba+n';                     -> 1mysql> SELECT
                    'Bn' REGEXP '^Ba+n';                      -> 0

a?

Match either zero or one a character.

mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0

de|abc

Match either of the sequences de or abc.

mysql> SELECT 'pi' REGEXP
                    'pi|apa';                     -> 1mysql> SELECT
                    'axe' REGEXP 'pi|apa';                    -> 0mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

(abc)*

Match zero or more instances of the sequence abc.

mysql> SELECT 'pi' REGEXP
                    '^(pi)*$';                    -> 1mysql> SELECT
                    'pip' REGEXP '^(pi)*$';                   -> 0mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1

{1}, {2,3}

{n} or {m,n} notation provides a more general way of writing regular expressions that match many occurrences of the previous atom (or "piece") of the pattern. m and n are integers.

To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive.

m and n must be in the range from 0 to RE_DUP_MAX (default 255), inclusive. If both m and n are given, m must be less than or equal to n.

mysql> SELECT 'abcde' REGEXP
                    'a[bcd]{2}e';              -> 0mysql> SELECT
                    'abcde' REGEXP 'a[bcd]{3}e';              -> 1mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1

[a-dX], [^a-dX]

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.

mysql> SELECT 'aXbc' REGEXP
                    '[a-dXYZ]';                 -> 1mysql> SELECT
                    'aXbc' REGEXP '^[a-dXYZ]$';               -> 0mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

[.characters.]

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.

mysql> SELECT '~' REGEXP
                    '[[.~.]]';                     -> 1mysql> SELECT
                    '~' REGEXP '[[.tilde.]]';                 -> 1

[=character_class=]

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.

mysql> SELECT 'justalnums' REGEXP
                    '[[:alnum:]]+';       -> 1mysql> SELECT '!!'
                    REGEXP '[[:alnum:]]+';               -> 0

[[:<:]], [[:>:]]

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 (_).

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0