Use REGEXP Regular Expressions in MySQL.

Use REGEXP Regular Expressions in MySQL.

In the past, I used like to search for data. Later I found that mysql also had regular expressions and felt better than like. Next I will share with you the Usage Details of mysql REGEXP regular expressions, I hope this method will be helpful to you.

MySQL adopts the Regular Expression of Henry Spencer, and its goal is to comply with POSIX 1003.2. See Appendix C: Thank you. MySQL adopts an extended version to support the pattern matching operation used with the REGEXP operator in SQL statements. See section 3.3.4.7 "pattern matching ".

In this Appendix, we summarize the special characters and structures that can be used for REGEXP operations in MySQL, and provide some examples. This appendix does not contain all the details that can be found on Henry Spencer's regex (7) manual page. This manual page is included in the regex.7 file in the regex directory of the MySQL source code distribution edition.

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

Extraordinary Regular Expressions use special structures so that they can match more than one string. 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 that starts with B, ends with s, and contains any number of a or n characters.

The following table modes can be used with the regexp operator.

Application Example: Find the user records with Incorrect Email format in the User table:

SELECT * FROM usersWHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'

The regular expression syntax in MySQL database, mainly including the meaning of various symbols.

(^) Character

Matches the start position of a string. For example, "^ a" indicates a string starting with the letter.

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

Query whether the xxxyyy string starts with xx. The result value is 1, indicating that the value is true, which meets the conditions.

($) Character

The end position of the matched string. For example, "X ^" indicates the string ending with the letter X.

(.) Character

This character is an English dot. It matches any character, including carriage return and line feed.

(*) Character

The asterisk matches 0 or multiple characters and must have content before it. For example:

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

In this SQL statement, the regular expression match is true.

(+) Characters

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

(?) Character

The question mark matches 0 times or 1 time.

Instance:

According to the preceding table, different types of SQL queries can be installed to meet the requirements. Here are some understandings. Consider that we have a table named person_tbl and a field named name:

The query finds all names starting with 'st '.

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

All names found in the query end with 'OK'

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

Query all strings with the name package 'mar'

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Query all names whose names start 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:

^

Start of the string following the matched string

Mysql> select "fonfo" REGEXP "^ fo $";-> 0 (unmatched) mysql> select "fofo" REGEXP "^ fo";-> 1 (matched)

$

The end of the matched string

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

Match any characters (including new lines)

Mysql> select "fofo" REGEXP "^ f. * ";-> 1 (matching) mysql> select" fonfo "REGEXP" ^ f. * ";-> 1 (indicating 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 (MATCH) mysql> select "Bn" REGEXP "^ Ba * n";-> 1 (MATCH)

A +

Match any number of a (not including empty strings)

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

A?

Match one or zero

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

De | abc

Match de or abc

Mysql> select "pi" REGEXP "pi | apa";-> 1 (matching) mysql> select "axe" REGEXP "pi | apa "; -> 0 (indicating unmatched) mysql> select "apa" REGEXP "pi | apa";-> 1 (indicating matched) mysql> select "apa" REGEXP "^ (pi | apa) $";-> 1 (matching) mysql> select "pi" REGEXP "^ (pi | apa) $ ";-> 1 (matching) mysql> select" pix "REGEXP" ^ (pi | apa) $ ";-> 0 (not matching)

(Abc )*

Match any number of abc (including empty strings)

Mysql> select "pi" REGEXP "^ (pi) * $";-> 1 (matching) mysql> select "pip" REGEXP "^ (pi) * $ "; -> 0 (indicating unmatched) mysql> select "pipi" REGEXP "^ (pi) * $";-> 1 (indicating matched)

{1}
{2, 3}

This is a more comprehensive method, which can implement the functions of the previous several 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 {}, indicating that the character can only appear I times; there is an integer parameter I in {}, followed by a ",", it indicates that the character can appear for I or more times. In {}, there is only one integer parameter I, followed by a ",", followed by an integer parameter j, it indicates that the character can only appear more than I times, 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 (default value: 255 ). If two parameters exist, the second parameter must be greater than or equal to the first parameter.

[A-dX]

Match "a", "B", "c", "d", or "X"

[^ A-dX]

Matches any character except "a", "B", "c", "d", and "X.

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

Mysql> select "aXbc" REGEXP "[a-dXYZ]";-> 1 (matching) mysql> select "aXbc" REGEXP "^ [a-dXYZ] $ "; -> 0 (indicating unmatched) mysql> select "aXbc" REGEXP "^ [a-dXYZ] + $";-> 1 (indicating matched) mysql> select "aXbc" REGEXP "^ [^ a-dXYZ] + $";-> 0 (indicating no matching) mysql> select "gheis" REGEXP "^ [^ a-dXYZ] + $";-> 1 (indicating matching) mysql> select "gheisa" REGEXP "^ [^ a-dXYZ] + $";-> 0 (indicating mismatch)