Description of the MySQL regular expression

Regular Expressions (regex) are a powerful tool for defining complex queries.


here is a simple information that ignores some of the detailed information.


A regular expression defines a rule for a string. The simplest regular expression does not contain any reserved words. For example, the regular expression Hello is only matched to the string "Hello".


Regular expressions use some special structure, so it can match more strings. For example, regular expression Hello|word can match both the string "Hello" and the string "word". To give a more complicated example, the regular expression b[an]*s can match the string "Bananas", "Baaaaas", "Bs", and any other string that begins with B with the end of S, and the middle can include any combination of a and any n.


You can use the following reserved words in a regular expression


^


matches a string that starts with the following string


mysql> Select "Fonfo" REGEXP "^fo$"; -> 0 (indicates mismatch)


mysql> Select "Fofo" REGEXP "^FO"; -> 1 (matching)


$


matches the string at the end of the preceding string


mysql> Select "Fono" REGEXP "^fono$"; -> 1 (matching)


mysql> Select "Fono" REGEXP "^fo$"; -> 0 (indicates mismatch)


　　.


matches any character (including new rows)


mysql> Select "Fofo" REGEXP "^f.*"; -> 1 (matching)


mysql> Select "Fonfo" REGEXP "^f.*"; -> 1 (matching)


A *


Match any number of a (including empty strings)


mysql> Select "Ban" REGEXP "^ba*n"; -> 1 (matching)


mysql> Select "Baaan" REGEXP "^ba*n"; -> 1 (matching)


mysql> Select "Bn" REGEXP "^ba*n"; -> 1 (matching)


+


matches any number of a (excluding empty strings)


mysql> Select "Ban" REGEXP "^ba+n"; -> 1 (matching)


mysql> Select "Bn" REGEXP "^ba+n"; -> 0 (indicates mismatch)


a?


match one or 0 a


mysql> Select "Bn" REGEXP "^ba?n"; -> 1 (matching)


mysql> Select "Ban" REGEXP "^ba?n"; -> 1 (matching)


mysql> Select "Baan" REGEXP "^ba?n"; -> 0 (indicates mismatch)


DE|ABC


match de or ABC


mysql> Select "PI" REGEXP "Pi|apa"; -> 1 (matching)


mysql> Select "Axe" REGEXP "Pi|apa"; -> 0 (indicates mismatch)


mysql> Select "APA" REGEXP "Pi|apa"; -> 1 (matching)


Mysql> Select "APA" REGEXP "^ (Pi|apa) $"; -> 1 (matching)


mysql> Select "PI" REGEXP "^ (Pi|apa) $"; -> 1 (matching)


mysql> Select "Pix" REGEXP "^ (Pi|apa) $"; -> 0 (indicates mismatch)


(ABC) *


matches any number of ABC (including Empty strings)


mysql> Select "PI" REGEXP "^ (pi) *$"; -> 1 (matching)


mysql> Select "Pip" REGEXP "^ (pi) *$"; -> 0 (indicates mismatch)


mysql> Select "Pipi" REGEXP "^ (pi) *$"; -> 1 (matching)


{1}


{2,3}


This is a more comprehensive approach that enables the functionality of several reserved words in the previous


A *


can be written as a{0,}


+


can be written as A{1,}


a?


can be written a{0,1}


only one integer parameter I in {}, which indicates that the character can only appear I; within {} There is an integer parameter I, followed by a ",", which indicates that the character can appear I or I, and that there is only one integer parameter in {}, followed by a ",", and then an integer parameter J, Indicates that the character can only appear more than I, J times below (including I and J times). The integer argument must be greater than or equal to 0, and less than or equal to Re_dup_max (default is 255). If there are two parameters, the second must be greater than or equal to the first


[A-DX]


matches "A", "B", "C", "D" or "X"


[^A-DX]


matches any character except "A", "B", "C", "D", "X". "[", "]" must be in pairs using


mysql> Select "AxBC" REGEXP "[a-dxyz]"; -> 1 (matching)


mysql> Select "AxBC" REGEXP "^[a-dxyz]$"; -> 0 (indicates mismatch)


mysql> Select "AxBC" REGEXP "^[a-dxyz]+$"; -> 1 (matching)


mysql> Select "AxBC" REGEXP "^[^a-dxyz]+$"; -> 0 (indicates mismatch)


mysql> Select "Gheis" REGEXP "^[^a-dxyz]+$"; -> 1 (matching)


mysql> Select "Gheisa" REGEXP "^[^a-dxyz]+$"; -> 0 (indicates mismatch)


　　------------------------------------------------------------


[[. characters.]]


represents the order of the comparison elements. The order of characters within parentheses is unique. But the parentheses can contain wildcards, so he can match more characters. For example: Regular expressions [[. Ch.]] *c matches the first five characters of the CHCHCC.


[=character_class=]


represents an equal class that can replace other equal elements in a class, including itself. For example, if O and (+) are members of an equal class, then [[=o=]], [[= (+] =]] and [O (+)] are completely equivalent.


[: Character_class:]


in parentheses, in [: And:] The middle is the name of the character class, which can represent all characters belonging to this class.


The names of the character classes are: alnum, digit, punct, alpha, graph, space, blank, lower, upper, cntrl, print, and Xdigit


mysql> Select "Justalnums" REGEXP "[: alnum:]]+"; -> 1 (matching)


mysql> Select "!!" REGEXP "[[: alnum:]]+"; -> 0 (indicates mismatch)


　　[[::]]


an empty string that matches the beginning and end of a word, and neither the beginning nor the end of the word is a character contained in Alnum nor an underscore.


mysql> Select "A word a" REGEXP "[[::]]"; -> 1 (matching)


mysql> Select "A Xword a" REGEXP "[[::]]"; -> 0 (indicates mismatch)


mysql> Select "weeknights" REGEXP "^ (Wee|week) (knights|nights) $"; -> 1 (matching)