Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 285.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Regular Expressions

12.5.2 Regular Expressions

Table 12.9 Regular Expression 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 also Section 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's regex(7) manual page. That manual page is included in MySQL source distributions, in the regex.7 file under the regex directory.

Regular Expression Operators

  • expr NOT REGEXP pat, expr NOT RLIKE pat

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

  • expr REGEXP pat, expr RLIKE pat

    Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If either expr or pat is NULL, the return value is NULL.

    RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

    The pattern can be an extended regular expression, the syntax for which is discussed in Regular Expression Syntax. 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 arguments.

    Regular expression operations use the character set and collation of the string expression and pattern 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.8.4, “Collation Coercibility in Expressions”. If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings.

    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 |
    +---------------------+
    mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
    +----------------+-----------------------+
    | 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' |
    +----------------+-----------------------+
    |              1 |                     0 |
    +----------------+-----------------------+
    Warning

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

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:

  • ^

    Match the beginning of a string.

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

    Match the end of a string.

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

    Match any character (including carriage return and newline).

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

    Match any sequence of zero or more a characters.

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

    Match any sequence of one or more a characters.

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

    Match either zero or one a character.

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

    Alternation; match either of the sequences de or abc.

    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
  • (abc)*

    Match zero or more instances of the sequence abc.

    mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1
    mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0
    mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1
  • {1}, {2,3}

    Repetition; {n} and {m,n} notation provide 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.

    • a*

      Can be written as a{0,}.

    • a+

      Can be written as a{1,}.

    • a?

      Can be written as a{0,1}.

    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. If both m and n are given, m must be less than or equal to n.

    m and n must be in the range from 0 to RE_DUP_MAX (default 255), inclusive.

    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
  • [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]';                 -> 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
  • [.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.

    Name Character Name Character
    NUL 0 SOH 001
    STX 002 ETX 003
    EOT 004 ENQ 005
    ACK 006 BEL 007
    alert 007 BS 010
    backspace '\b' HT 011
    tab '\t' LF 012
    newline '\n' VT 013
    vertical-tab '\v' FF 014
    form-feed '\f' CR 015
    carriage-return '\r' SO 016
    SI 017 DLE 020
    DC1 021 DC2 022
    DC3 023 DC4 024
    NAK 025 SYN 026
    ETB 027 CAN 030
    EM 031 SUB 032
    ESC 033 IS4 034
    FS 034 IS3 035
    GS 035 IS2 036
    RS 036 IS1 037
    US 037 space ' '
    exclamation-mark '!' quotation-mark '"'
    number-sign '#' dollar-sign '$'
    percent-sign '%' ampersand '&'
    apostrophe '\'' left-parenthesis '('
    right-parenthesis ')' asterisk '*'
    plus-sign '+' comma ','
    hyphen '-' hyphen-minus '-'
    period '.' full-stop '.'
    slash '/' solidus '/'
    zero '0' one '1'
    two '2' three '3'
    four '4' five '5'
    six '6' seven '7'
    eight '8' nine '9'
    colon ':' semicolon ';'
    less-than-sign '<' equals-sign '='
    greater-than-sign '>' question-mark '?'
    commercial-at '@' left-square-bracket '['
    backslash '\\' reverse-solidus '\\'
    right-square-bracket ']' circumflex '^'
    circumflex-accent '^' underscore '_'
    low-line '_' grave-accent '`'
    left-brace '{' left-curly-bracket '{'
    vertical-line '|' right-brace '}'
    right-curly-bracket '}' tilde '~'
    DEL 177
    mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1
    mysql> 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.

    Character Class Name Meaning
    alnum Alphanumeric characters
    alpha Alphabetic characters
    blank Whitespace characters
    cntrl Control characters
    digit Digit characters
    graph Graphic characters
    lower Lowercase alphabetic characters
    print Graphic or space characters
    punct Punctuation characters
    space Space, tab, newline, and carriage return
    upper Uppercase alphabetic characters
    xdigit Hexadecimal digit characters
    mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1
    mysql> 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[[:>:]]';   -> 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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by ehab heikal on September 21, 2010
For those times when you need a fast reference for regex you can download and print or view this pdf http://www.elmotaheda.com/website-design-development/regex_cheat_cheat .

The sheet is color coded and is very easy to read.
  Posted by Kasey Speakman on November 10, 2010
It's quite unfortunate that their REGEX interpreter doesn't support logical NOT expressions. Ordinarily I would write something like this to match all numbers except 11:

(?!^11$)^\d+$

But with their implementation, I have to make a "do not match" field as well as a "match" field to make sure my conditions are met. :/
  Posted by Andrey Klyuchnikov on March 1, 2011
re: Kasey Speakman

^([0-9]|[02-9][02-9]|[0-9][02-9]|[02-9][0-9]|[0-9]{3,})$

:)
  Posted by Naved Shah on May 13, 2011
For LIKE Results

SELECT * FROM REG where seg_3 REGEXP 'SHA|S11|A11|SH4|SH4H|H4H|5HA|5HAH|HAH'

SNUSHAH

  Posted by Jon Spriggs on November 24, 2011
I struggled with the [:character_type:] element of the regexp. I had a problem where users were entering artist names with various degrees of spacing in the name... for example "Loudog" and "Lou Dog".

To get around this, I changed the string being searched for, in code, to: "[:space:]*L[:space:]*o[:space:]*u[:space:]*D[:space:]*o[:space:]*g[:space:]*" but this wasn't working.

Eventually someone spotted that I was essentially searching for zero-or-more instances of the characters :, s, p, a, c or e. This search should instead have been: "[[:space:]]*L[[:space:]]*o[[:space:]]*u[[:space:]]*D[[:space:]]*o[[:space:]]*g[[:space:]]*"

It's not clear in the examples above that this is what you should be searching for. This is roughly equivelent to "/\s+L\s+o\s+u\s+D\s+o\s+g\s+/i"

  Posted by Kasey Speakman on December 8, 2011
@Andrey Klyuchnikov

Using ORed decompositions is fine for my simple example, but in actual data, the length of the decompositions may be well beyond the point where they would be worth doing over just using two fields... or better yet if MySQL would implement the negation operator ?!.

Also, decompositions are much less clear in what they are doing. It's pretty obvious what the negation operator is doing. When I have to revisit a regex 2 years from now to add something to it, I'm going to want to smack someone that used a decomp.
  Posted by Eric Kent on December 23, 2011
If you need an IsNumeric or IsInt function, you can use:
return sValue regexp '^-?[0-9]+[.]?[0-9]*$|^-?[.][0-9]+$';
or
return sValue regexp '^-?[0-9]+$';
where sValue is a char argument.
  Posted by Naftali Zakharov on May 2, 2013
Ehab Heikal, thanks for your link to the regex cheat sheet. Only, the content is no longer there. I am aware of some other regex cheat sheets that seem very popular and I am sharing a short list here.

1) A pretty neutral cheat sheet for regexes.
The download is for free.
http://www.cheatography.com/davechild/cheat-sheets/regular-expressions/
and there is a nicer-looking one for a symbolic fee, here:
http://www.addedbytes.com/cheat-sheets/regular-expressions-cheat-sheet/

2) While we are at it, here's a MySQL cheat sheet by the same author:
http://www.addedbytes.com/download/mysql-cheat-sheet-v1/pdf/

3) Another regex cheat sheet:
http://www.regular-expressions.info/reference.html

Hope this is helpful.
Sign Up Login You must be logged in to post a comment.