MySQL regexp regular expressions using the encyclopedia _ Regular Expressions

I used to find the data are using like later found in MySQL also have regular expressions and feel better than like, I would like to share with you the MySQL regexp regular expression use of detailed, I hope this method is helpful.

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.

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

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.

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 mismatch) 
mysql> Select "Fofo" REGEXP "^fo";-> 1 (indicates match)

$

The matched string ends with the preceding string

Mysql> Select "Fono" REGEXP "^fono$"; -> 1 (matching) 
mysql> select "Fono" REGEXP "^fo$";-> 0 (means mismatch) 

Match any character (including new rows)

mysql> Select "Fofo" 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 (indicates match)

A +

Match any number of a (excluding empty strings)

mysql> Select "Ban" REGEXP "^ba+n"; -> 1 (matching) 

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 mismatch) 
mysql> Select "APA" REGEXP "Pi|apa";-> 1 (Match) 
mysql> select "APA" REGEXP "^ (Pi|apa) $";-> 1 (matching) 
mysql> select "PI" REGEXP "^ (Pi|apa) $";-& Gt 1 (Match) 
mysql> select "Pix" REGEXP "^ (Pi|apa) $";-> 0 (indicates mismatch)

(ABC) *

Match any number of ABC (including Empty strings)

mysql> Select "PI" REGEXP "^ (pi) *$"; -> 1 (matching) 
mysql> select "Pip" REGEXP "^ (pi) *$";-> 0 (indicates mismatch) 

{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 (for match) mysql> select "AxBC" REGEXP "^[a-dxyz]$"; -> 0 (indicates mismatch) mysql> select "AxBC" REGEXP "^[a-dxyz]+$"; -> 1 (matching) mysql> select "AxBC" REGEXP "^[^a-dxyz]+$"; -> 0 (indicates mismatch) mysql> select "Gheis" REGEXP "^[^a-dxyz]+$"; -> 1 (matching) mysql> select "Gheisa" REGEXP "^[^a-dxyz]+$"; -> 0 (indicates no match)