11.5.2. Regular Expressions

Table 11.9. String Regular Expression Operators

NameDescription
NOT REGEXPNegation of REGEXP
REGEXPPattern matching using regular expressions
RLIKESynonym for REGEXP

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

  • 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 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 is not case sensitive, except when used with binary strings.

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

    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:

  • ^

    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

    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}

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

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

    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';              -> 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.

    NameCharacterNameCharacter
    NUL0SOH001
    STX002ETX003
    EOT004ENQ005
    ACK006BEL007
    alert007BS010
    backspace'\b'HT011
    tab'\t'LF012
    newline'\n'VT013
    vertical-tab'\v'FF014
    form-feed'\f'CR015
    carriage-return'\r'SO016
    SI017DLE020
    DC1021DC2022
    DC3023DC4024
    NAK025SYN026
    ETB027CAN030
    EM031SUB032
    ESC033IS4034
    FS034IS3035
    GS035IS2036
    RS036IS1037
    US037space' '
    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'~'
    DEL177  
    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 NameMeaning
    alnumAlphanumeric characters
    alphaAlphabetic characters
    blankWhitespace characters
    cntrlControl characters
    digitDigit characters
    graphGraphic characters
    lowerLowercase alphabetic characters
    printGraphic or space characters
    punctPunctuation characters
    spaceSpace, tab, newline, and carriage return
    upperUppercase alphabetic characters
    xdigitHexadecimal 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
Copyright © 2010-2017 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout