Use regular expression notes in MySQL

Regular expressions are widely used in various program programming places, in the MySQL SQL statements using regular expressions can also solve a lot of query problems, to simplify, and clever use can improve the efficiency of MySQL query. This article describes how to use the regexp syntax in MySQL to run SQL statements that contain regular expressions.

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000|2000 '
Order BY Prod_name;
In MySQL SQL statements use regular expressions, where needed, with the RegExp keyword, as shown in the example, the regexp regular expression is ' 1000|2000 ', which means 1000 or 2000, which is the result of the Query Prod_name field 1000 or 2000.

Regular expression rules in MySQL are roughly the same as regular expression rules elsewhere, such as "\" Escape, "|" Or, the representation of letters and numbers is the same.

Another example:

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' \ ([0-9] sticks?\\) '
Order BY Prod_name;
The results can be found for example:

(1 sticks)

(3 sticks)

(4)

Description: "\ (", "\)" Means escape, contains "(", ")". The question mark indicates dispensable.

The following reserved words can be used in a regular expression
^
The matched string starts with the following string
mysql> Select "Fonfo" RegExp "^fo$"; -> 0 (indicates no match)
mysql> Select "Fofo" RegExp "^FO"; -> 1 (matching)
$
The matched string ends with the preceding string
Mysql> Select "Fono" RegExp "^fono$"; -> 1 (matching)
Mysql> Select "Fono" RegExp "^fo$"; -> 0 (indicates no match)
.
Match 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)
A +
Match any number of a (excluding empty strings)
mysql> Select "Ban" RegExp "^ba+n"; -> 1 (matching)
Mysql> select "bn" RegExp "^ba+n"; -> 0 (indicates no match)
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 no match)
De|abc
Match de or ABC
mysql> Select "PI" RegExp "Pi|apa"; -> 1 (matching)
Mysql> select "Axe" RegExp "Pi|apa"; -> 0 (indicates no match)
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 no match)
(ABC) *
Match any number of ABC (including Empty strings)
mysql> Select "PI" regexp "^ (pi) *$"; -> 1 (matching)
mysql> Select "Pip" regexp "^ (pi) *$"; -> 0 (indicates no match)
mysql> Select "Pipi" regexp "^ (pi) *$"; -> 1 (matching)
{1}
{2,3}
This is a more comprehensive approach that enables you to implement several of the 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 indicates that the character can only appear I; there is an integer parameter I in {}. followed by a ",", indicating that the character can appear I or I more than, in {} Only an integer parameter I, followed by a ",", and then an integer parameter J, indicating 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]
Match "A", "B", "C", "D" or "X"
[^A-DX]
Matches any character other than "A", "B", "C", "D", "X". "[", "]" must be used in pairs
mysql> Select "AxBC" RegExp "[a-dxyz]"; -> 1 (matching)
mysql> Select "AxBC" RegExp "^[a-dxyz]$"; -> 0 (indicates no match)
mysql> Select "AxBC" RegExp "^[a-dxyz]+$"; -> 1 (matching)
mysql> Select "AxBC" RegExp "^[^a-dxyz]+$"; -> 0 (indicates no match)
mysql> Select "Gheis" RegExp "^[^a-dxyz]+$"; -> 1 (matching)
mysql> Select "Gheisa" RegExp "^[^a-dxyz]+$"; -> 0 (indicates no match)
------------------------------------------------------------
[[. characters.]]
Represents the order in which elements are compared. 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=]
A class that represents equality and 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:]
Inside the brackets, 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 no match)
[[::]]
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 that is contained in alnum and cannot be underlined.
Mysql> Select "A word a" regexp "[[::]]"; -> 1 (matching)
Mysql> Select "A Xword a" regexp "[[::]]"; -> 0 (indicates no match)
Mysql> Select "Weeknights" RegExp "^ (Wee|week) (knights|nights) $"; -> 1 (matching)

This is the basic usage of using regular expressions in MySQL.