Mysql regular REGEXP learning exercise notes

REGEXP is a function used to execute Regular Expressions in mysql, such as the preg function in php. If regexp is just a simple query, use like, however, you still need to use regexp. Let's take a look.

MySql user manual suggestionsWhen constructing a simple query, wildcards are still used.

For example, Select [* | fieldname list] From [tablename] where [fieldname] like ["% someletter" | "% someletter % ","_","? Someletter "];

However, in some special queries, regular expressions are not required. MYSQL provides the following regular expressions:
REGEXP, RLIKE, NOT RLIKE
Use these three replace the original LIKE predicates, which can be followed by regular expressions.

For example, to query data whose field contains "_", use the following query statement:

SELECT * from tablename where fieldname rlike '. [_].';

Some Characters of the extended regular expression are:

· '.' Matches any single character.

· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.

· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.

If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern 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 start of the pattern or "$" at the end of the pattern ".
To find the name starting with "B", use "^" to match the start of the name:

Use Regular Expressions

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, I encountered such a problem in the application,
There is a field t1 with values similar :,

You need to find out from it: for example, the range of numbers before the first comma is 3-5, and the range of numbers before the third comma is 3-5, the value range before the first 10th commas is 3-5, and the rest are between 1-5...

The SQL statement can be written as follows:

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 like comparison operators (note that the = or! = );

2. The mode is case-insensitive by default;

3. "_" can be used to match any single character. "%" can match any number of characters (including zero characters );

Comes with some mysql regular rules

^ Match the start part of the string

$ Match the end part of a string

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

A * matches any sequence of 0 or multiple a characters

A + matches any sequence of one or more a characters

A? Match 0 or 1 a character

De | abc matching sequence de or abc

(Abc) * matches 0 or multiple adc instances of a sequence.

 

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

A * can be written as a {0 ,}

A + can be written as a {1 ,}

A? It can be written as a {0, 1}

 

[A-dX] matches any character that is a, B, c, d, or X. The '-' character between two other characters forms a range.

[^ A-dX] matches any character that is not a, B, c, d, or X. The character '^' above indicates no.

 

[. Characters.] In a bracket expression (using [and]), match the character sequence used for checking the element. The character is a single character or a new character name.

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 its own,

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

 

[: 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 text and numeric characters

Alpha character

Blank white space characters

Cntrl control characters

Digit numeric characters

Graph character

Lower lowercase characters

Print graphics or space characters

Punct punctuation

Space, tab, new line, and carriage return

Upper uppercase characters

Xdigit hexadecimal numeric characters

 

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

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 'fang shan zi 'regexp '[[: <:] shan [[:]';-> 1

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

Mysql> select 'fang shans zi 'regexp '[[: <:] shan [[:]';-> 0

 

The regular expression uses special characters and should be preceded by two backslash ''characters.

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

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

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