MySQL SQL--Regular expression usage

In MySQL can be used to achieve a multiplier effect, such as a simple look at the example, using MySQL to filter the Chinese record of a field:

The special characters and structures used in MySQL for regexp operations, 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

SQL regular

SELECT * FROM table where not name RegExp ' ^[1-9a-za-z] ';

Instance

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

In parentheses expressions (using [ and ]), match the sequence of characters used for proofing elements. A character is a single character or a character name such as a new line. In file regexp/cname.h , you can find a complete list of character names.

SELECT ' ~ ' REGEXP ' [[. ~]]] '; -> 1

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

· [=character_class=]

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

· [: character_class:]

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

Alnum	Literal numeric character
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 characters
MySpace	Spaces, tabs, new lines, and carriage returns
Upper	Uppercase text characters
Xdigit	hexadecimal numeric characters

They represent the character classes defined on the CType (3) manual page. other class names may be provided in a particular region. a character class must not be used as the endpoint of a range.

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

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

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

These tokens represent the word boundary. They match the start and end of Word, respectively. Word is a series of character characters that have no characters before and after them. Character characters are alphanumeric characters or underscores (_) in the Alnum class.

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

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 Resolver is responsible for interpreting one of these, 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:

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

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

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