MySQL :: MySQL 5.7 Reference Manual :: 12.8.2 Regular Expressions (original) (raw)

12.8.2 Regular Expressions

Table 12.14 Regular Expression Functions and Operators

Name Description
NOT REGEXP Negation of REGEXP
REGEXP Whether string matches regular expression
RLIKE Whether string matches regular expression

A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations. See alsoSection 3.3.4.7, “Pattern Matching”.

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 regular expression pattern-matching operations in SQL statements. This section does not contain all the details that can be found in Henry Spencer'sregex(7) manual page. That manual page is included in MySQL source distributions, in theregex.7 file under theregex directory.

Regular Expression Function and Operator Descriptions

mysql> SELECT 'Michael!' REGEXP '.*';  
+------------------------+  
| 'Michael!' REGEXP '.*' |  
+------------------------+  
|                      1 |  
+------------------------+  
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';  
+---------------------------------------+  
| 'new*\n*line' REGEXP 'new\\*.\\*line' |  
+---------------------------------------+  
|                                     0 |  
+---------------------------------------+  
mysql> SELECT 'a' REGEXP '^[a-d]';  
+---------------------+  
| 'a' REGEXP '^[a-d]' |  
+---------------------+  
|                   1 |  
+---------------------+

Warning
The REGEXP andRLIKE operators work in byte-wise fashion, so they are not multibyte safe and may produce unexpected results with multibyte 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.

Regular Expression Syntax

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 expressionhello 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|world contains the| alternation operator and matches either the hello or world.

As a more complex example, the regular expressionB[an]*s matches any of the stringsBananas, Baaaaas,Bs, and any other string starting with aB, ending with an s, and containing any number of a orn characters in between.

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

mysql> SELECT 'fo\nfo' REGEXP '^fo$';                   -> 0  
mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1  
mysql> SELECT 'fo\no' REGEXP '^fo\no$';                 -> 1  
mysql> SELECT 'fo\no' REGEXP '^fo$';                    -> 0  
mysql> SELECT 'fofo' REGEXP '^f.*$';                    -> 1  
mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';                -> 1  
mysql> SELECT 'Ban' REGEXP '^Ba*n';                     -> 1  
mysql> SELECT 'Baaan' REGEXP '^Ba*n';                   -> 1  
mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1  
mysql> SELECT 'Ban' REGEXP '^Ba+n';                     -> 1  
mysql> SELECT 'Bn' REGEXP '^Ba+n';                      -> 0  
mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1  
mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1  
mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0  
mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1  
mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0  
mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1  
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1  
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1  
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0  
mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1  
mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0  
mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1  
mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';              -> 0  
mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';              -> 1  
mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1  
mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1  
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0  
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1  
mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0  
mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1  
mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0  
mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1  
mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1  
mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1  
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0  
mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1  
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0

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:

mysql> SELECT '1+2' REGEXP '1+2';                       -> 0
mysql> SELECT '1+2' REGEXP '1\+2';                      -> 0
mysql> SELECT '1+2' REGEXP '1\\+2';                     -> 1