Regular expressions define rules for a string. The simplest regular expression does not contain any reserved words. For example, the regular expression hello only matches the string "hello".
General regular expressions use some special structure, so they can match more strings. For example, the regular expression hello|word can match both the string "hello" and the string "word". To give a more complex example, the regular expression b[an]*s can match the strings "bananas", "baaaaas", "bs", and any other strings that start with b and end with s, and can include any combination of a and any n.
The following reserved words can be used in a regular expression
^
The beginning of the string after the matching string
mysql> select "fonfo" regexp "^fo$"; -> 0 (indicates mismatch)
mysql> select "fofo" regexp "^fo"; -> 1 (indicates matching)
$
The end of the string before the matching string
mysql> select "fono" regexp "^fono$"; -> 1 (indicates matching)
mysql> select "fono" regexp "^fo$"; -> 0 (indicates mismatch)
.
Match any character (including new lines)
mysql> select "fofo" regexp "^f.*"; -> 1 (indicates matching)
mysql> select "fonfo" regexp "^f.*"; -> 1 (indicates matching)
a*
Match any multiple a (including empty strings)
mysql> select "ban" regexp "^ba*n"; -> 1 (indicates matching)
mysql> select "baaan" regexp "^ba*n"; -> 1 (indicates matching)
mysql> select "bn" regexp "^ba*n"; -> 1 (indicates matching)
a+
Match any multiple a (excluding empty strings)
mysql> select "ban" regexp "^ba+n"; -> 1 (indicates matching)
mysql> select "bn" regexp "^ba+n"; -> 0 (indicates mismatch)
a?
Match one or zero a
mysql> select "bn" regexp "^ba?n"; -> 1 (indicates matching)
mysql> select "ban" regexp "^ba?n"; -> 1 (indicates matching)
mysql> select "baan" regexp "^ba?n"; -> 0 (indicates mismatch)
de|abc
Match de or abc
mysql> select "pi" regexp "pi|apa"; -> 1 (indicates matching)
mysql> select "axe" regexp "pi|apa"; -> 0 (indicates mismatch)
mysql> select "apa" regexp "pi|apa"; -> 1 (indicates matching)
mysql> select "apa" regexp "^(pi|apa)$"; -> 1 (indicates matching)
mysql> select "pi" regexp "^(pi|apa)$"; -> 1 (indicates matching)
mysql> select "pix" regexp "^(pi|apa)$"; -> 0 (indicates mismatch)
(abc)*
Match any multiple abcs (including empty strings)
mysql> select "pi" regexp "^(pi)*$"; -> 1 (indicates matching)
mysql> select "pip" regexp "^(pi)*$"; -> 0 (indicates mismatch)
mysql> select "pipi" regexp "^(pi)*$"; -> 1 (indicates matching)
{1}
{2,3}
This is a more comprehensive method, which can implement the functions of several previous reserved words.
a*
Can be written as a{0,}
a+
Can be written as a{1,}
a?
Can be written as a{0,1}
There is only one integer parameter i in {}, which means that the character can only appear i times; there is an integer parameter i in {}, followed by a "," to indicate that the character can appear i times or more; there is only one integer parameter i, followed by a "," and followed by an integer parameter j, which means that the character can only appear i times, and below j times (including i times and j times). The integer parameter must be greater than or equal to 0, 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 one.
[a-dx]
Match "a", "b", "c", "d" or "x"
[^a-dx]
Match any character except "a", "b", "c", "d", "x". "[" and "]" must be used in pairs
mysql> select "axbc" regexp "[a-dxyz]"; -> 1 (indicates matching)
mysql> select "axbc" regexp "^[a-dxyz]$"; -> 0 (indicates mismatch)
mysql> select "axbc" regexp "^[a-dxyz]+$"; -> 1 (indicates matching)
mysql> select "axbc" regexp "^[^a-dxyz]+$"; -> 0 (indicates mismatch)
mysql> select "gheis" regexp "^[^a-dxyz]+$"; -> 1 (indicates matching)
mysql> select "gheisa" regexp "^[^a-dxyz]+$"; -> 0 (indicates mismatch)
------------------------------------------------------------
[[.characters.]]
Represents the order of comparison elements. The order of characters in brackets is unique. But the brackets can contain wildcards, so it can match more characters. For example: the regular expression [[.ch.]]*c matches the first five characters of chchcc.
[=character_class=]
A class that represents an equality can replace other equal elements in the 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, between [: and :] is the name of the character class, which can represent all characters belonging to this class.
The names of character classes are: alnum, digit, punct, alpha, graph, space, blank, lower, upper, cntrl, print and xdigit
mysql> select "justalnums" regexp "[[:alnum:]]+"; -> 1 (indicates matching)
mysql> select "!!" regexp "[[:alnum:]]+"; -> 0 (indicates mismatch)
[[::]]
Match an empty string at the beginning and end of a word, and the characters that are not included in the alnum or underscore.
mysql> select "a word a" regexp "[[::]]"; -> 1 (indicates matching)
mysql> select "a xword a" regexp "[[::]]"; -> 0 (indicates mismatch)
mysql> select "weeknights" regexp "^(wee|week)(knights|nights)$"; -> 1 (indicates matching)