MySQL Regular expression regexp detailed

Source: Internet
Author: User
Tags character classes control characters explode alphanumeric characters

Before we start this topic we first do a small experiment, compare regexp and like them two which is high efficiency, if the efficiency is too low, we do not need to do too much research, the experiment code is as follows:
<?php
Require ("config.php");
Functions: Timing functions
Usage: Echo Runtime (1);
Function Runtime ($mode =0) {
Static $s;
IF (! $mode) {
$s =microtime ();
Return;
}
$e =microtime ();
$s =explode ("", $s);
$e =explode ("", $e);
Return Sprintf ("%.2f MS", ($e [1]+ $e [0]-$s [1]-$s [0]) *1000);
}

The following results are 2300 ms to 2330 MS
for ($i =0; $i <10000; $i + +) {
$Rs = $Mysql->get ("select * from ' Hotel ' where address REGEXP ' ^ River County | Taoyuan City '");
}

The following results are 2300 ms to 2330 MS
for ($i =0; $i <10000; $i + +) {
$Rs = $Mysql->get ("select * from ' Hotel ' where address like ' River County% ' or address like '% Peach Garden City ');
}

Echo Runtime (1);
The result of the experiment is very surprising, that is, their efficiency is almost the same, the fluctuation is not dozens of milliseconds, then we need to do in-depth study of RegExp, Because he can greatly shorten the length of the SQL statement. This query can be a good example of how to shorten SQL length for long query conditions.If you want to download this article, please note that it is free from the copyright colorful, I do not like to see the people who have uploaded my work and do not mention it QQ9256114
Regular expressions are a powerful way to specify patterns for complex searches.

MySQL is implemented with Henry Spencer's regular expression, which targets 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, we summarize the special characters and structures that can be used for regexp operations in MySQL, and give some examples. This appendix does not contain all the details that can be found on the Regex (7) manual page of Henry Spencer. This manual page is included in the MySQL source distribution, 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 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. If you want to download this article, please note that it is free from the copyright colorful, I do not like to see the people who have uploaded my work and do not mention it QQ9256114

For the regexp operator, the regular expression can use any of the following special characters and structures:

· ^

Matches the starting part of the string.

mysql> SELECT ' fo\nfo ' REGEXP ' ^fo$ ';                      -0mysql> SELECT ' fofo ' REGEXP ' ^fo '; 1

· $

Matches the ending part of the string.

mysql> SELECT ' fo\no ' REGEXP ' ^fo\no$ ';                    -1mysql> SELECT ' fo\no ' REGEXP ' ^fo$ '; 0

· .

Matches any character (including carriage return and new line).

mysql> SELECT ' fofo ' REGEXP ' ^f.*$ ';                -1mysql> SELECT ' fo\r\nfo ' REGEXP ' ^f.*$ '; 1

· A *

Matches any sequence of 0 or more a characters.

mysql> SELECT ' Ban ' REGEXP ' ^ba*n ';                   -1mysql> SELECT ' Baaan ' REGEXP ' ^ba*n ';                      -1mysql> SELECT ' Bn ' REGEXP ' ^ba*n '; 1

· A +

Matches any sequence of 1 or more a characters.

mysql> SELECT ' Ban ' REGEXP ' ^ba+n ';                      -1mysql> SELECT ' Bn ' REGEXP ' ^ba+n '; 0

· A?

Match 0 or 1 a characters.

mysql> SELECT ' Bn ' REGEXP ' ^ba?n ';                     -1mysql> SELECT ' Ban ' REGEXP ' ^ba?n ';                    -1mysql> SELECT ' Baan ' REGEXP ' ^ba?n '; 0

· De|abc

Match the sequence de or ABC.

Mysql>SELECT ' pi ' REGEXP ' Pi|apa ';1mysql>SELECT ' axe ' REGEXP ' Pi|apa ';0mysql>SELECT ' apa ' REGEXP ' Pi|apa ';1mysql>SELECT ' apa ' REGEXP ' ^ (Pi|apa) $ ';1mysql>SELECT ' pi ' REGEXP ' ^ (Pi|apa) $ ';1mysql>SELECT ' pix ' REGEXP ' ^ (Pi|apa) $ ';0

· (ABC) *

Match 0 or more instances of a sequence ABC.

mysql> SELECT ' pi ' REGEXP ' ^ (pi) *$ ';                   -1mysql> SELECT ' pip ' REGEXP ' ^ (pi) *$ ';                  -0mysql> SELECT ' pipi ' REGEXP ' ^ (pi) *$ '; 1

· {1}, {2,3}

The {n} or {m,n} symbols provide a more general way to write regular expressions that match many of the aforementioned atoms (or "portions") of the pattern. Both M and n are integers.

o A *

Can be written as a{0,}.

o A +

Can be written as A{1,}.

o A?

Can be written as a{0,1}.

More precisely, a{n} matches the n instances of a exactly. A{n,} matches N or more instances of a. A{m,n} matches the m~n instance of A, containing M and N.

M and n must be in the range of 0~re_dup_max (default 255) and contain 0 and Re_dup_max. If both M and n,m are given, they must be less than or equal to N.

mysql> SELECT ' abcde ' REGEXP ' a[bcd]{2}e ';              -0mysql> SELECT ' abcde ' REGEXP ' a[bcd]{3}e ';           -1mysql> SELECT ' abcde ' REGEXP ' a[bcd]{1,10}e '; 1

· [A-DX], [^A-DX]

The

matches any character that is (or is not, if you use ^) A, B, C, D, or X. The "-" character between the two other characters forms a range that matches all characters from the beginning of the 1th character to the 2nd character. For example, [0-9] matches any decimal number. To include the literal character "]", it must be immediately after the opening parenthesis "[". To include the literal character "-", it must be written first or last. for [] Any character that does not have any special meaning defined internally, it only matches itself.

mysql>  SELECT ' axbc ' REGEXP ' [a-dxyz] ';                  1mysql >  SELECT ' axbc ' REGEXP ' ^[a-dxyz]$ ';                0mysql>  SELECT ' axbc ' REGEXP ' ^[a-dxyz]+$ ';               1mysql>  SELECT ' AxBC ' REGEXP ' ^[^a-dxyz]+$ ';              0mysql>  SELECT ' Gheis ' REGEXP ' ^[^a-dxyz]+$ ';             1mysql>  SELECT ' Gheisa ' REGEXP ' ^[^a-dxyz]+$ ';            0

· [. Characters.]

In the parentheses expression (using [and]), match the sequence of characters used to proofread the element. Characters are single characters or character names such as new lines. In file regexp/cname.h, you can find a complete list of character names.

mysql> SELECT ' ~ ' REGEXP ' [[. ~.]] ';                 1mysql> SELECT ' ~ ' REGEXP ' [[. Tilde.]] '; 1

· [=character_class=]

In the parentheses expression (using [and]), [=character_class=] represents the same type. It matches all characters that have the same collation value, including itself, for example, if O and (+) are members of the equivalent class, then [[[=o=]], [[= (+) =]], and [O (+)] are synonyms. The same class must not be used as a range endpoint.

· [: Character_class:]

In the parentheses expression (using [and]), [: Character_class:] represents the character class that matches all the characters of the term class. The standard class name is:

Alnum

Literal numeric characters

Alpha

Literal characters

Blank

White space characters

Cntrl

Control characters

Digit

numeric characters

Graph

Graphic characters

Lower

lowercase text characters

Print

Graphic or space character

Punct

Punctuation character

Space

Spaces, tabs, new lines, and carriage returns

Upper

Uppercase literal characters

Xdigit

hexadecimal numeric characters

They represent the character classes defined in the CType (3) manual page. Other class names may be available in specific regions. The character class must not be used as the endpoint of a range.

mysql> SELECT ' justalnums ' REGEXP ' [[: alnum:]]+ '; -1mysql> SELECT '! '               REGEXP ' [[: alnum:]]+ '; 0

· [[: <:]], [[:;:]]

These tokens represent word boundaries. They match the start and end of Word, respectively. Word is a series of character characters, preceded and followed by no word characters. Character characters are alphanumeric characters or underscores (_) in the Alnum class.

mysql> SELECT ' A word a ' REGEXP ' [[: <:]]word[[:>:]] ';  1mysql> SELECT ' A xword a ' REGEXP ' [[: <:]]word[[:>:]] '; 0

To use a literal instance of a special character in a regular expression, precede it with a 2 backslash "\" character. The MySQL parser is responsible for interpreting one, and the regular expression Library is responsible for interpreting the other. For example, to match the string "1+2" that contains the special character "+", only the last one is correct in the following regular expression:

mysql> SELECT ' 1+2 ' REGEXP ' 1+2 ';                      -0mysql> SELECT ' 1+2 ' REGEXP ' 1\+2 ';                     -0mysql> SELECT ' 1+2 ' REGEXP ' 1\\+2 '; 1

MySQL Regular expression regexp detailed

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.