The use of regexp regular expressions in MySQL

MySQL is implemented with the regular expression of Henry Spencer, with the goal of POSIX 1003.2. See Appendix C: Thank you. MySQL uses an extended version to support pattern matching operations that are used with the regexp operator in SQL statements. See section 3.3.4.7, "pattern matching."

In this appendix, the special characters and structures that can be used for regexp operations in MySQL are summarized, and some examples are given. This appendix does not contain all the details that can be found on the Regex (7) manual page of Henry Spencer. The manual page is included in the MySQL source distribution and is located in the regex.7 file in the Regex directory.

A regular expression describes a set of strings. The simplest regular expression is a regular expression that does not contain any special characters. For example, the regular expression hello matches hello.

Non-trivial regular expressions take a particular structure, allowing them to match more than 1 strings. For example, the regular expression Hello|word matches a string of hello or string word.

As a more complex example, the regular expression b[an]*s matches any of the following strings: Bananas,baaaaas,bs, and any other string that starts with B, ends with S, and contains any number A or n characters in it.

The following is a pattern that can be used for tables that follow the regexp operator.

Mode	what pattern match?
^	The beginning of the string
$	End of string
.	Any single character
[...]	Any list of characters in square brackets
[^...]	Any character not listed in square brackets
P1|p2|p3	Alternately match any pattern p1,p2 or P3
*	0 or more preceding elements
+	One or more instances of the preceding element
N	n instances of the preceding element
{M,n}	Elements in front of M to n instances

Application example to find the user record in the user table with an incorrect email format:

The code is as follows	Copy Code
SELECT * From users WHERE Email not REGEXP ' ^[a-z0-9._%-]+@[a-z0-9.-]+. [A-z] {2,4}$ '

MySQL database is the syntax of the expression, mainly including the meaning of various symbols.

(^) Character
Matches the start position of a string, such as "^a", which represents a string that begins with the letter A.

The code is as follows	Copy Code
mysql> select ' xxxyyy ' regexp ' ^xx '; +-----------------------+ | ' xxxyyy ' regexp ' ^xx ' | +-----------------------+ | 1 | +-----------------------+ 1 row in Set (0.00 sec)

The query xxxyyy the string to start with XX, the result value is 1, and the value is true to satisfy the condition.

($) Character
Matches the end position of a string, such as "x^", which represents a string ending with the letter X.

(.) Character
This character is the point in English, it matches any one character, including carriage return, newline, etc.

(*) character
The asterisk matches 0 or more characters before it must have content. Such as:
mysql> select ' xxxyyy ' regexp ' x* ';
This SQL statement, the regular match is true.

(+) character
The plus sign matches 1 or more characters and must have content before it. The plus sign is similar to the use of the asterisk, except that the asterisk allows 0 occurrences, and the plus sign must appear at least once.

(?) character
The question mark matches 0 or 1 times.

Instance:
Now, depending on the table above, you can install a variety of different types of SQL queries to meet your requirements. Here is a list of some of the understandings. Consider that we have a table for PERSON_TBL and have a field name named:

The query finds all the names with the ' st ' start

mysql> SELECT name from person_tbl WHERE name REGEXP ' ^st ';

The query finds all the names ending with ' OK '

mysql> SELECT name from person_tbl WHERE name REGEXP ' ok$ ';

The query finds all the names that enclose the ' mar ' string

mysql> SELECT name from person_tbl WHERE name REGEXP ' Mar ';

The query finds all the names that start with a vowel and end with ' OK '

mysql> SELECT name from person_tbl WHERE name REGEXP ' ^[aeiou]|ok$ ';
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)