MySQL Regular expression usage detailed

We've seen MySQL's like pattern matching and ... % MySQL supports another type of pattern-matching operation based on regular expressions and regexp operators. If you know PHP or Perl, it's very simple, because these scripts have very similar regular expression matches.

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

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$ ';

Attention:

About the difference between like and regexp: Kind matches the entire column. If the matched text appears only in the column value, like does not find it, the corresponding row is not returned (except, of course, with wildcards). and RegExp matches within the column value, if the matched matching text appears in the column value, RegExp will find it, and the corresponding row is returned, a very important difference (of course, if you adapt to the glyphs ^ and $, you can implement regexp matching the entire column rather than a subset of the columns).
Case-by-case distinction: MySQL regular expression matching (after version 3.23.4) is case-insensitive. If you want to be case-sensitive, you should use the BINARY keyword, such as where Post_name REGEXP BINARY ' Hello. 000 '

2. About or matching
To search for one of two strings, use |, for example, select * from the products where pro_id REGEXP ' 1000|2000 ' ORDER by pro_id;
So that both 1000 and 2000 can match and return, of course, using multiple | can match multiple strings

3. Just match a few characters []
For example, this will match [0123456789] to match 0 to 9,[1-4][4-9] is also a valid range. In addition, the range is not necessarily numeric, [A-z] matches any alphabetic character
For example: SELECT prod_name from the products where Prod_name REGEXP ' [1-5] Ton ' ORDER by Prod_name;

4. Match Special characters using to escape
\. ability to match.
\f Page Change
\ n Line Change
\ r Carriage Return
\ t tab
\ Portrait Tabulation

Note: To match itself, you need to use \

5. Matching character classes
[: Alnum:] Arbitrary letters and numbers (pass [a-za-z0-9])
[: Alpha:] any character (same [a-za-z])
[: Blank:] Spaces and tabs (same as [\ t])
[:d igit:] any number (same [0-9])
[: Lower:] Any lowercase letter
[: Upper:] Any capital letter
[: space:] Any white space character including spaces

6. Match multiple instances, about repeating metacharacters
* 0 or more matches
+ 1 or more matches (equal to {1,})
？ 1 or more matches (equal to {0,1})
{n} specified number of matches
{n,} not less than the specified number of matches
{N,m} matches the number of ranges (m not more than 255)

Example: Select Prod_name from the products where Prod_name REGEXP ' [[:D igit:]]{4} ', as mentioned earlier, [:d Igit:] matches any prime submodules, and thus it is a geometry of the number. {4} exactly requires that the preceding character (any number) appear 3 this, so [[:d igit:]]{4} matches any 4 digits connected together, of course, the example above can also be written regexp ' [0-9][0-9][0-9][0-9] '

7. Locator character
^ Beginning of the text
& End of text
[[:;:]] The beginning of the word
[[::]] The end of the word
By using these locators, you can make RegExp act like.

A regular expression uses some special structure, so it can match more strings. For example, regular
The expression Hello|word can match both the string "Hello" and the string "word".
To give a more complicated example, the regular expression b[an]*s can match the string "Bananas", "baaaaa
S
, "BS", and any other string that starts with B and ends with S, the middle can include any group of A and any n
Collection
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 more than the first, in {} There is only an integer parameter I,
Followed by a ",", and then an integer parameter J, which indicates that the character can only appear more than I, J times the following
(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 25
5).
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)
------------------------------------------------------------
[[. characters.]]
Represents the order in which elements are compared. The order of characters within parentheses is unique. However, the parentheses can contain wildcard characters,
So he can match more characters. For example: Regular expressions [[. Ch.]] *c matches the first five characters of CHCHCC
。
[=character_class=]
A class that represents equality and can replace other equal elements in a class, including itself. For example, if O and (+) are
A member of an equal class, then [[=o=]], [[= (+] =]] and [O (+)] are completely equivalent.
[: Character_class:]
Inside the brackets, in [: And:] The middle is the name of the character class, which can represent all characters belonging to this class.
The names of the character classes are: alnum, digit, punct, alpha, graph, space, blank, lower, Uppe
R, Cntrl, print and Xdigit
mysql> Select "Justalnums" RegExp "[: alnum:]]+"; -> 1 (matching)
mysql> select "!!" RegExp "[: alnum:]]+"; -> 0 (indicates no match)
[[:;:]]
[[::]]
An empty string that matches the beginning and end of a word, and the beginning and end of the word are not included in the Alnum
Character can not be underlined.
Mysql> Select "A word a" regexp "[[: <:]]word[[:>:]]"; -> 1 (matching)
Mysql> Select "A Xword a" regexp "[[: <:]]word[[:>:]]"; -> 0 (indicates no match)
Mysql> Select "Weeknights" RegExp "^ (Wee|week) (knights|nights) $"; -> 1 (indicates
Match