Shulou(Shulou.com)05/31 Report--

This article will explain in detail the examples of the use of REGEXP regular expressions in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

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

Non-trivial regular expressions have a special structure that enables them to match more than one string. For example, the regular expression hello | word matches the string hello or the 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 begins with B, ends with s, and contains any number of an or n characters.

The following is the schema that can be used for tables with the REGEXP operator.

Apply an example to find Email malformed user records in the user table:

SELECT * FROM usersWHERE email NOT REGEXP'^ [A-Z0-9.9% -] + @ [A-Z0-9.9 -] +. [Amurz] {2jue 4} $'

The syntax of regular expressions in MySQL database mainly includes the meaning of various symbols.

(^) character

Matches the beginning of a string, such as "^ a" for a string that begins with the letter A.

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

Query whether the xxxyyy string begins with xx, and the result value is 1, indicating that the value is true, which meets the condition.

($) character

Matches the end of a string, such as "X ^" for a string that ends with the letter X.

(.) character

This character is the dot under English, and it matches any character, including carriage return, line feeds, and so on.

(*) character

The asterisk matches 0 or more characters and must have content before it. Such as:

Mysql > select 'xxxyyy' regexp'

For this SQL statement, the regular match is true.

(+) character

The plus sign matches one or more characters and must also have content before it. The use of the plus sign is similar to that of the asterisk, except that the asterisk is allowed 0 times and the plus sign must appear at least once.

(?) character

The question mark matches 0 or 1 times.

Example:

Now, according to the above table, you can install different types of SQL queries to meet the requirements. List some understandings here. Consider that we have a table named person _ tbl and a field named name:

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

Mysql > SELECT name FROM person_tbl WHERE name REGEXP'^ st'

The query finds all the names that end with 'ok''

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'ok$'

The query finds all the strings of the name packet 'mar''

Mysql > SELECT name FROM person_tbl WHERE name REGEXP 'mar'

The query finds all names that begin with vowels 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 matching string begins with the following string

Mysql > select "fonfo" REGEXP "^ fo$";-> 0 (for mismatch) mysql > select "fofo" REGEXP "^ fo";-> 1 (for match)

$

The matching string ends with the preceding string

Mysql > select "fono" REGEXP "^ fono$";-> 1 (for match) mysql > select "fono" REGEXP "^ fo$";-> 0 (for mismatch).

Match any characters (including new lines)

Mysql > select "fofo" REGEXP "^ f.*";-> 1 (for matching) mysql > select "fonfo" REGEXP "^ f.*";-> 1 (for matching)

A *

Match any number of a (including empty strings)

Mysql > select "Ban" REGEXP "^ Ba*n";-> 1 (for match) mysql > select "Baaan" REGEXP "^ Ba*n";-> 1 (for match) mysql > select "Bn" REGEXP "^ Ba*n";-> 1 (for match)

A +

Match any number of a (excluding empty strings)

Mysql > select "Ban" REGEXP "^ Ba+n";-> 1 (for match) mysql > select "Bn" REGEXP "^ Ba+n";-> 0 (for mismatch)

A?

Match one or zero a

Mysql > select "Bn" REGEXP "^ Ba?n";-> 1 (for matching) mysql > select "Ban" REGEXP "^ Ba?n";-> 1 (for matching) mysql > select "Baan" REGEXP "^ Ba?n";-> 0 (for mismatch)

De | abc

Match de or abc

Mysql > select "pi" REGEXP "pi | apa";-> 1 (for matching) mysql > select "axe" REGEXP "pi | apa";-> 0 (for mismatch) mysql > select "apa" REGEXP "pi | apa";-> 1 (for matching) mysql > select "apa" REGEXP "^ (pi | apa) $";-> 1 (for matching) mysql > select "pi" REGEXP "^ (pi | apa) $";-> 1 (for matching) mysql > select "pix" REGEXP "^ (pi | apa) $" -> 0 (indicates mismatch)

(abc) *

Match any number of abc (including empty strings)

Mysql > select "pi" REGEXP "^ (pi) * $";-> 1 (for match) mysql > select "pip" REGEXP "^ (pi) * $";-> 0 (for mismatch) mysql > select "pipi" REGEXP "^ (pi) * $";-> 1 (for match)

{1}

{2,3}

This is a more comprehensive approach, which can achieve the functions of several previous reserved words.

A *

It can be written as a {0,}

A +

Can be written as a {1,}

A?

It can be written as a {0jue 1}

There is only one integer parameter I in {}, which means that the character can only appear I times; in {}, there is an integer parameter I, followed by a ",", which means that the character can appear I times or more; in {}, there is only one integer parameter I, followed by a ",", followed by an integer parameter j, indicating that the character can only appear more than I times, and less than j times (including I times and j times). The integer parameter must be greater than or equal to 0 and less than or equal to RE_DUP_MAX (the 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 except "a", "b", "c", "d", "X".

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

Mysql > select "aXbc" REGEXP "[a-dXYZ]";-> 1 (for matching) mysql > select "aXbc" REGEXP "^ [a-dXYZ] $";-> 0 (for mismatch) mysql > select "aXbc" REGEXP "^ [a-dXYZ] + $";-> 1 (for matching) mysql > select "aXbc" REGEXP "^ [^ a-dXYZ] + $";-> 0 (for mismatch) mysql > select "gheis" REGEXP "^ [^ a-dXYZ] + $" -> 1 (for matching) mysql > select "gheisa" REGEXP "^ [^ a-dXYZ] + $";-> 0 (for mismatch) on "examples of the use of REGEXP regular expressions in MySQL" this article ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.