RegExp regular expressions in MySQL use Daquan

I used to look for data are using like later found that there are regular expressions in MySQL and feel better than like, let me share with you the MySQL regexp regular expression using a detailed, I hope this method is helpful to everyone.

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 use special specific structures that allow them to match more than 1 strings. For example, the regular expression Hello|word matches the string 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 starting with B, ending with S, and containing any number of a or n characters.

The following is the pattern of tables that can be used with the regexp operator.

Mode	What pattern matches
^	Start of string
$	End of string
.	Any single character
[...]	List of any characters within square brackets
[^...]	Any character that is not listed in square brackets
P1|p2|p3	Alternately matches any pattern p1,p2 or P3
*	0 or more of the preceding elements
+	One or more instances of the preceding element
N	n instances of the preceding element
{M,n}	m to n elements in front of an instance

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

1 2 3

SELECT * From users WHERE Email not REGEXP ' ^[a-z0-9._%-][email protected][a-z0-9.-]+. [A-z] {2,4}$ '

The syntax of the regular expressions in MySQL database, mainly including the meanings of various symbols.

(^) Character

The starting position of the matching string, such as "^a", represents a string that begins with the letter A.

1 2 3 4 5 6 7 8

mysql> select ' xxxyyy ' regexp ' ^xx '; +-----------------------+ | ' xxxyyy ' regexp ' ^xx ' | +-----------------------+ | 1 | +-----------------------+ 1 row in Set (0.00 sec)

Queries whether the xxxyyy string starts with XX and the result value is 1, which indicates that the value is true and satisfies the condition.

($) Character

Matches the ending position of the string, such as "x^", which represents the string ending with the letter X.

(.) Character

This character is the English point, it matches any one character, including carriage return, newline, and so on.

(*) character

Asterisks match 0 or more characters, and must have content before it. Such as:

1	mysql> select ' xxxyyy ' regexp ' x* ';

This SQL statement matches the regular match to true.

(+) character

The plus sign matches 1 or more characters, and there must be content before it. The plus sign is similar to the use of asterisks, except that asterisks allow 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, various types of SQL queries can be installed to meet the requirements. Here is a list of some understandings. Consider that we have a table for Person_tbl and a field named name:

Query find all names beginning with ' St '

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

Query to find all names ending with ' OK '

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

The query finds all the names of the letters ' mar ' strings

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

The query finds all names ending with a vowel start and ' OK '

1	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 begins with a string that follows

1 2	mysql> Select "Fonfo" REGEXP "^fo$"; -0 (indicates mismatch) mysql> Select "Fofo" REGEXP "^FO"; --1 (indicates a match)

$

The matched string ends with the preceding string

1 2 3

Mysql> Select "Fono" REGEXP "^fono$"; --1 (indicates a match) Mysql> Select "Fono" REGEXP "^fo$"; -0 (indicates mismatch) .

Match any character (including new lines)

1 2	mysql> Select "Fofo" REGEXP "^f.*"; --1 (indicates a match) mysql> Select "Fonfo" REGEXP "^f.*"; --1 (indicates a match)

A *

Match any number of Aces (including empty strings)

1 2 3

mysql> Select "Ban" REGEXP "^ba*n"; --1 (indicates a match) mysql> Select "Baaan" REGEXP "^ba*n"; --1 (indicates a match) mysql> Select "Bn" REGEXP "^ba*n"; --1 (indicates a match)

A +

Match any number of a (not including empty strings)

1 2	mysql> Select "Ban" REGEXP "^ba+n"; --1 (indicates a match) mysql> Select "Bn" REGEXP "^ba+n"; -0 (indicates mismatch)

A?

Match one or 0 a

1 2 3

mysql> Select "Bn" REGEXP "^ba?n"; --1 (indicates a match) mysql> Select "Ban" REGEXP "^ba?n"; --1 (indicates a match) mysql> Select "Baan" REGEXP "^ba?n"; -0 (indicates mismatch)

De|abc

Match de or ABC

1 2 3 4 5 6

mysql> Select "PI" REGEXP "Pi|apa"; --1 (indicates a match) Mysql> select "Axe" REGEXP "Pi|apa"; -0 (indicates mismatch) Mysql> Select "APA" REGEXP "Pi|apa"; --1 (indicates a match) Mysql> Select "APA" REGEXP "^ (Pi|apa) $"; --1 (indicates a match) mysql> Select "PI" REGEXP "^ (Pi|apa) $"; --1 (indicates a match) mysql> select "Pix" REGEXP "^ (Pi|apa) $"; -0 (indicates mismatch)

(ABC) *

Match any number of ABC (including Empty strings)

1 2 3

mysql> Select "PI" REGEXP "^ (pi) *$"; --1 (indicates a match) mysql> Select "Pip" REGEXP "^ (pi) *$"; -0 (indicates mismatch) mysql> Select "Pipi" REGEXP "^ (pi) *$"; --1 (indicates a match)

{1}
{2,3}

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

A *

Can be written as a{0,}

A +

Can be written as A{1,}

A?

can be written a{0,1}

There is only one integer parameter I within {}, indicates that the character can only appear I times, in {} There is an integer parameter I, followed by a ",", indicating that the character can appear I or more than one time, in {} There is only an integer parameter I, followed by a "," followed by an integer parameter J, indicating that the character can only appear more than I, The following J times (including I and J times). The integral type 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

[A-DX]

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

[^A-DX]

Matches any character except "A", "B", "C", "D", "X".

"[", "]" must be used in pairs

1 2 3 4 5 6

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

Match character class (POSIX character class)

Use it when you need to add a layer [], for example [[:d igit:]]

Class	Description
[: Alnum:]	Any letters and numbers (same as [a-za-z0-9])
[: Alpha:]	Any letter (same as [a-za-z])
[: Blank:]	Spaces and tabulation (same as [\\t])
[: Cntrl:]	ASCII control characters (ASCII0 to 31 and 127)
[:d Igit:]	Any number (same as [0-9])
[: Graph:]	Same as [[:p rint:]] but does not contain spaces
[: Lower:]	Any lowercase letter (same as [A-z])
[:p rint:]	any printable character
[:p UNCT:]	is not in [[: Alnum:]] and not in [[: Cntrl:]] the character
[: Space:]	Any white space character, including spaces (same as [\\f\\n\\r\\t\\v])
[: Upper:]	Any uppercase letter (same as [A-z])
[: Xdigit:]	Any 16 binary digits (same as [a-fa-f0-9])

1. SELECT * from ' mytable '
2. Where name REGEXP ' name[[:d igit:]] ';

Output

Name1

Name6

RegExp regular expressions in MySQL use Daquan