Mysql Regular Expressions 正则表达式

Regular Expressions

Table 12.9 String Regular Expression Operators

Name Description
NOT
REGEXP
Negation of REGEXP
REGEXP Pattern matching using regular expressions
RLIKE Synonym 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)

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.

语法:

使用 (NOT)REGEXP 运算符。

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

参考: http://dev.mysql.com/doc/refman/5.5/en/regexp.html

  •  [.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
  • [=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 thectype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.
字符类不是使用标准的\w等形式,而是使用[[:alpha:]]。
匹配一般的特殊字符可以直接使用转义后的字符匹配,如: ‘[‘  REGEXP  ‘\\[‘ ,但是匹配如 \r,\n,\t,\b等字符必须使用 [[.\n.]] 的mysql特殊形式。
[[.\b.]] 与标准的 \b 不同,其不是匹配一个边界,仅仅是匹配“退格”符;反斜杠”\” 在正则表达式中似乎只能作为元字符的转义字符,常规情况下的特殊字符必须使用 [[. 和 .]] 括起来,如匹配换行符与匹配\b相同,使用 ‘\b’  REGEXP  ‘[[.\b.]]’ 。请特别注意Mysql字面量层的转义和正则表达式层的转义,所以一般会有两个\
使用 [[:<:]] 和 [[:>:]] 匹配单词的前后边界。

发表评论

电子邮件地址不会被公开。