Mysql Regular Expression instructions

Mysql regular expressions I personally think that the regular expressions in other program scripts are not much different in usage and rules. Next I will summarize some of the mysql Regular Expressions and instance methods.

"." Matches any single character.

A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.
Regular Expressions are case-sensitive, but if you want to, you can use one character class matching method. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.
If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".
To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:
To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

For the REGEXP operator, regular expressions can use any of the following special characters and structures:

· ^

Match the start part of the string.

Mysql> SELECT 'fonfo 'regexp '^ fo $';-> 0

Mysql> SELECT 'fofofo' REGEXP '^ fo';-> 1

· $

Matches the end part of the string.

Mysql> SELECT 'fono' REGEXP '^ fono $';-> 1

Mysql> SELECT 'fono' REGEXP '^ fo $';-> 0

·.

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

Mysql> SELECT 'fofofo' REGEXP '^ f. * $';-> 1

Mysql> SELECT 'forinfo' REGEXP '^ f. * $';-> 1

· *

Matches any sequence of 0 or multiple a characters.

Mysql> SELECT 'Ban 'regexp' ^ Ba * n';-> 1

Mysql> SELECT 'baa' REGEXP '^ Ba * n';-> 1

Mysql> SELECT 'bn 'regexp' ^ Ba * n';-> 1

· A +

Matches any sequence of one or more a characters.

Mysql> SELECT 'Ban 'regexp' ^ Ba + N';-> 1

Mysql> SELECT 'bn 'regexp' ^ Ba + N';-> 0

·?

Matches 0 or 1 a character.

Mysql> SELECT 'bn 'regexp '^ Ba? N';-> 1

Mysql> SELECT 'Ban 'regexp' ^ Ba? N';-> 1

Mysql> SELECT 'baance' REGEXP '^ Ba? N';-> 0

· De | abc

Match the sequence de or abc.

Mysql> SELECT 'Pi 'REGEXP 'Pi | apa';-> 1

Mysql> SELECT 'axe' REGEXP 'Pi | apa ';-> 0

Mysql> SELECT 'apa 'regexp 'Pi | apa ';-> 1

Mysql> SELECT 'apa 'regexp '^ (pi | apa) $';-> 1

Mysql> SELECT 'Pi 'regexp '^ (pi | apa) $';-> 1

Mysql> SELECT 'pix 'regexp' ^ (pi | apa) $ ';-> 0

· (Abc )*

Matches 0 or multiple instances of the sequence abc.

Mysql> SELECT 'Pi 'regexp' ^ (pi) * $ ';-> 1

Mysql> SELECT 'pip 'regexp' ^ (pi) * $ ';-> 0

Mysql> SELECT 'pipi' REGEXP '^ (pi) * $';-> 1

· {1}, {2, 3}

The {n} or {m, n} symbol provides a more common way to write regular expressions and can match many of the aforementioned atoms (or "parts") of the pattern "). Both m and n are integers.

O *

Can be written as a {0 ,}.

O a +

Can be written as a {1 ,}.

O?

Can be written as a {0, 1 }.

More accurately, a {n} exactly matches n instances of. A {n,} matches n or more instances of. A {m, n} matches m ~ of ~ N instances, including m and n.

M and n must be between 0 and ~ The range of RE_DUP_MAX (255 by default) includes 0 and RE_DUP_MAX. If both m and n are given, m must be less than or equal to n.

Mysql> SELECT 'abcde' REGEXP 'a [bcd] {2} E';-> 0

Mysql> SELECT 'abcde' REGEXP 'a [bcd] {3} E';-> 1

Mysql> SELECT 'abcde' REGEXP 'a [bcd] {1, 10} E';-> 1

· [A-dX], [^ a-dX]

Match any character that is (or is not, if ^ is used) a, B, c, d, or X. The "-" character between two other characters constitutes a range, and matches all characters starting from 1st characters to 2nd characters. For example, [0-9] matches any decimal number. To include the text character "]", it must be followed by the brackets. To contain the text character "-", it must be written first or last. For any character that does not define any special meaning for [], it only matches itself.

Mysql> SELECT 'axbc' REGEXP '[a-dXYZ]';-> 1

Mysql> SELECT 'axbc' REGEXP '^ [a-dXYZ] $';-> 0

Mysql> SELECT 'axbc' REGEXP '^ [a-dXYZ] + $';-> 1

Mysql> SELECT 'axbc' REGEXP '^ [^ a-dXYZ] + $';-> 0

Mysql> SELECT 'gheis 'regexp' ^ [^ a-dXYZ] + $ ';-> 1

Mysql> SELECT 'gheisa 'regexp' ^ [^ a-dXYZ] + $ ';-> 0

· [. Characters.]

In the brackets expression (using [and]), match the character sequence used for checking the element. The character is a single character or a new line or another character name. In the regexp/cname. h file, you can find the complete list of character names.

Mysql> SELECT '~ 'Regexp' [[...] ';-> 1

Mysql> SELECT '~ 'Regexp' [[. tilde.] ';-> 1

· [= Character_class =]

In the brackets (using [and]), [= character_class =] indicates the same type. It matches all characters with the same value, including itself. For example, if both o and (+) are similar members, [[= o =], [[= (+) =], and [o (+)] are synonyms. And so on.

· [: Character_class:]

In a bracket expression (using [and]), [: character_class:] indicates the character class that matches all characters in the term class. The standard class name is:

Alnum
 

Character

Alpha
 

Character

Blank
 

White space characters

Cntrl
 

Control characters

Digit
 

Numeric characters

Graph
 

Graphical characters

Lower
 

Lowercase characters

Print
 

Graphical or space characters

Punct
 

Punctuation

Space
 

Space, tab, new line, and carriage return

Upper
 

Uppercase characters

Xdigit
 

Hexadecimal numeric characters

They represent character classes defined on the ctype (3) manual page. Other class names may be provided for specific regions. Character classes cannot be used as endpoints of a range.

Mysql> SELECT 'justnums' 'regexp '[[: alnum:] +';-> 1

Mysql> SELECT '!! 'Regexp' [[: alnum:] + ';-> 0

· [[: <:], [[:>:]

These tags indicate word boundaries. They match the start and end of word respectively. Word is a series of character characters, with no character at the front and back. The character is a letter, digit, or underscore (_) in the alnum class (_).

Mysql> SELECT 'A word a' REGEXP '[[: <:] word [[: >:]]';-> 1

Mysql> SELECT 'a xword a' REGEXP '[[: <:] word [[: >:]]';-> 0

To use a text instance with special characters in a regular expression, add two backslash (") characters before it. The MySQL parser is responsible for interpreting one of them, and the regular expression library is responsible for interpreting the other. For example, to match the string "1 + 2" that contains the special character "+", in the following regular expression, only the last one is correct:

Mysql> SELECT '1 + 2' REGEXP '1 + 2';-> 0

Mysql> SELECT '1 + 2' REGEXP '1 + 2';-> 0

Mysql> SELECT '1 + 2' REGEXP '1 \ + 2';-> 1

Query instances:

SELECT * FROM table WHERE parent REGEXP '^ 0, 1, [0-9] *, $'