MySQL regular regexp learn to practice notes

The MySQL user manual suggests that you still use wildcards when you construct a simple query.

such as: Select [*|fieldname list] from [TableName] where [fieldname] like ["%someletter" | " %someletter% "," _ "," Someletter "];

However, in some special queries, it is not possible to use regular expressions. MySQL provides the regular expression where predicate has three, respectively:
REGEXP, Rlike, not rlike
Replace the original like predicate with these three, followed by the regular expression.

For example, to query for data that contains "_" in a field, use the following query statement:

SELECT * FROM tablename WHERE FIELDNAME rlike '. [_].';

Some characters of the extended regular expression are:

· ‘.’ matches any single character.

· Character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name the range of characters, use a "-". "[A-z]" matches any letter, and "[0-9]" matches any number.

· "*" matches 0 or more characters in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of any characters.

If the regexp pattern matches anywhere that is tested, the pattern matches (unlike like pattern matching, which matches only the entire value).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the beginning of the pattern or at the end of the pattern with "$".
To find the name that begins with "B", use "^" to match the beginning of the name:

Use regular

SELECT * from pet WHERE name REGEXP BINARY ' ^b ';
SELECT * from pet WHERE name REGEXP ' fy$ ';
SELECT * from pet WHERE name REGEXP ' W ';
SELECT * from pet WHERE name REGEXP ' ^.....$ ';
SELECT * from pet WHERE name REGEXP ' ^. {5}$ ';

today in the application of this problem,
there is a field T1 with a value similar to the following: 1,1,1,2,3,3,4,4,5,5,2,4,3,2,1,2

Need to search from the inside for example: the first comma before the number range is 3-5, the third comma before the range of digits between 3-5, the 10th comma before the number range of 3-5, the rest of the 1-5 between ...

The SQL statement can be written like this:

SELECT * from TB WHERE T1 REGEXP ' ^[3-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5 ],[1-5],[1-5]% ';

1. Use the like and not as comparison operators (note that you cannot use = or!=);

2. The default mode is to ignore the case;

3. Allow the use of "_" to match any single character, "%" matches any number of characters (including 0 characters);

With some MySQL regular rules

^ Start part of matching string

$ match End of string

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

A * matches any sequence of 0 or more a characters

A + matches any sequence of 1 or more a characters

A? Match 0 or 1 a characters

DE|ABC matching sequence de or ABC

(ABC) * Matching 0 or more instances of the sequence ADC

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

A * can be written as a{0,}

A + can be written as A{1,}

A? Can be written as a{0,1}

[A-DX] matches any character that is a,b,c,d or X, and the '-' character between two other characters constitutes a range

[^A-DX] matches any character that is not a,b,c,d or x, the preceding character ' ^ ' is the negative meaning

[. Characters.] In parentheses expressions (using [and]), match the sequence of characters used for proofing elements, character names such as single characters or new lines

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

mysql> 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,

[[=a=]] is equivalent to [a (+)],[a+],[a{1,}]

[: 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 whitespace characters

Cntrl control characters

Digit numeric characters

Graph graphic Characters

Lower lowercase text characters

Print graphic or space character

Punct punctuation Characters

Space spaces, tabs, new lines, and carriage returns

Upper uppercase literal characters

Xdigit hexadecimal digit character

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

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. The character is an alphanumeric character or an underscore (_) in the Alnum class.

Mysql> select ' Fang Shan zi ' regexp ' [[: <:]]shan[[:>:]] '; -> 1

Mysql> select ' Fang Shan zi ' regexp ' [[: <:]]fang[[:>:]] '; -> 1

Mysql> select ' Fang Shans zi ' regexp ' [[: <:]]shan[[:>:]] '; -> 0

Regular expressions use special characters that precede them with a 2 backslash ' character

mysql> SELECT ' 1+2′regexp ' 1+2′; -> 0

mysql> SELECT ' 1+2′regexp ' 1+2′; -> 0

mysql> SELECT ' 1+2′regexp ' 1\+2′; -> 1