MySQL complete collapse query regular matching detailed explanation

Overview

In the previous chapter, we learned about the filter conditions for queries. We learned that MySQL can perform fuzzy matching using the like % wildcard. Similarly, it also supports matching of other regular expressions. We use the REGEXP operator in MySQL to perform regular expression matching. Usage and like

Similar to , but much more powerful, and can achieve some very special and complex rule matching. When the regular expression is matched using the REGEXP command, 1 is returned if the match is true, and 0 is returned if the match is not true. If no matching rules are added by default, REGEXP is equivalent to like '%%'. Adding NOT in front (NOT REGEXP) is equivalent to NOT LIKE.

Matching pattern analysis

The following table lists the regular expression matching patterns that can be applied to the REGEXP operator. The description is relatively detailed. We will test them one by one later.

Matching Pattern	describe
^	Matches the beginning of the input string. If the Multiline property of the REGEXP object is set, ^ also matches the position after '\n' or '\r'.
$	Matches the end of the input string. If the Multiline property of the REGEXP object is set, $ also matches the position before '\n' or '\r'.
.	Matches any single character except "\n". To match any character including '\n', use a pattern of '[.\n]'.
[….]	A collection of characters. Matches any one of the contained characters. For example, '[abc]' can match 'a' in "plain".
[^...]	The set of non-matching characters. Matches any character not contained in the string. For example, '[^abc]' can match the 'p' in "plain".
[nm]	Matches any single character between m and n, such as [0-9], [az], [AZ]
*	Matches the preceding subexpression zero or more times. For example, a* matches "a" and "ab". * Equivalent to {0,}.
+	Matches the preceding subexpression one or more times. For example, 'a+' matches "ab" and "abc", but not "a". + is equivalent to {1,}.
?	Matches the preceding subexpression one or more times. For example, 'a?' matches "ab" and "a". ? is equivalent to {0,1}.
a1\| a2\|a3	Matches a1 or a2 or a3. For example, 'z\|food' matches "z" or "food". '(z\|f)ood' matches "zood" or "food".
{n}	n is a non-negative integer. Matches a certain number of times. For example, 'o{2}' does not match the 'o' in "Bob", but does match the two o's in "food".
{n,}	Matches the preceding subexpression n to multiple times. For example, 'o{2,}' matches not only "food" but also "foood".
{n,m}	n and m are both non-negative integers, where n <= m. Matches at least n times and at most m times.
{,m}	Matches the preceding subexpression 0 to m times
(….)	Element combination, that is, combining pattern elements into a single element, for example (do)* means matching 0 or more do

Matching pattern ^

Match the first part of the string. This matches the string starting with s. If it matches, return 1. If it does not match, return 0. Applied to the table, it returns the matching data.

mysql> select 'selina' REGEXP '^s';
+----------------------+
| 'selina' REGEXP '^s' |
+----------------------+
| 1 |
+----------------------+
1 row in set

mysql> select 'aelina' REGEXP '^s';
+----------------------+
| 'aelina' REGEXP '^s' |
+----------------------+
| 0 |
+----------------------+
1 row in set

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '^s';
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+---------+-----+
2 rows in set

Matching pattern $

Match from the end of the string, this matches data whose name ends with d.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'd$';
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
+----+-------+-----+---------+-----+
1 row in set

Matching pattern.

. matches any single character. The following script matches n followed by any character.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'n.';
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+---------+-----+
3 rows in set

Matching pattern [...]

It means matching any single character in the brackets, as long as at least one character meets the conditions. In the following example, only the names brand and weng can match b, w, and z.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP [bwz];
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[bwz]' at line 1
mysql> select * from user2 where name REGEXP '[bwz]';
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+---------+-----+
2 rows in set

Matching pattern [^...]

[^...] means to match any character not included in the string. For example, '[^brand]' can match 'h' in "helen", "s" in "sol", "w" in "weng", and "s" in "selina", but it cannot match "brand" and is therefore filtered out.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '[^brand]';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
4 rows in set

Matching mode [nm]

Matches any single character between m and n, such as [0-9], [az], [AZ]. In the following code, any element "sol" that is not between a - e is filtered out.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP '[ae]';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
4 rows in set

Matching pattern*

Matches the preceding subexpression zero or more times. For example, a* matches "a" and "ab". * Equivalent to {0,}. The following "e*g" will only match the name "weng".

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name REGEXP 'e*g';
+----+------+-----+---------+-----+
| id | name | age | address | sex |
+----+------+-----+---------+-----+
| 4 | weng | 33 | guizhou | 1 |
+----+------+-----+---------+-----+
1 row in set

Matching Mode+

Matches the preceding subexpression one or more times. For example, 'a+' matches "ab" and "abc", but not "a". + is equivalent to {1,}. As shown in the script below, the conditions are met by a combination of 1 to more n plus a d, and only "brand" and "annd" meet the conditions.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'n+d';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
2 rows in set

Matching mode?

Matches the preceding subexpression one or more times. For example, 'a?' matches "ab" and "a". ? is equivalent to {0,1}. e is 1 or 0, and is then restricted by l, so there are only three that meet the requirements.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'e?l';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
3 rows in set

Matches the pattern a1| a2|a3

Matches a1 or a2 or a3. For example, below, 'nn|en' can match "anny", "annd" and "helen", "weng" respectively.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'nn|en';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
4 rows in set

Matches the pattern {n} {n,} {n,m} {,m}

n and m are both non-negative integers, where n <= m. Matches at least n times and at most m times. m is empty and represents any number >= n, and n is empty and represents 0.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP 'n{2}';
+----+------+-----+----------+-----+
| id | name | age | address | sex |
+----+------+-----+----------+-----+
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+------+-----+----------+-----+
2 rows in set

mysql> select * from user2 where name REGEXP 'n{1,2}';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
6 rows in set

mysql> select * from user2 where name REGEXP 'l{1,}';
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+----------+-----+
3 rows in set

Matches a pattern(...)

Assuming the content of the brackets is abc, abc is matched as a whole, and the data that meets this rule is filtered out. Let's take an as an example and combine it with the knowledge learned above.

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+--------+-----+----------+-----+
7 rows in set

mysql> select * from user2 where name REGEXP '(an)+';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> select * from user2 where name REGEXP '(ann)+';
+----+------+-----+----------+-----+
| id | name | age | address | sex |
+----+------+-----+----------+-----+
| 6 | anny | 23 | shanghai | 0 |
| 7 | annd | 24 | shanghai | 1 |
+----+------+-----+----------+-----+
2 rows in set

mysql> select * from user2 where name REGEXP '(an).*d{1,2}';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 7 | annd | 24 | shanghai | 1 |
+----+-------+-----+----------+-----+
2 rows in set

Matches special characters \\

The regular expression language consists of special characters that have specific meanings. We have seen ., [], |, *, +, etc. How do we match these characters? In the following example, we use \\ to match special characters, \\ is the leading character, \\- means to search for -, and \\. means to search for ..

mysql> select * from user3;
+----+------+-------+
| id | age | name |
+----+------+-------+
| 1 | 20 | brand |
| 2 | 22 | sol |
| 3 | 20 | helen |
| 4 | 19.5 | diny |
+----+------+-------+
4 rows in set

mysql> select * from user3 where age REGEXP '[0-9]+\\.[0-9]+';
+----+------+------+
| id | age | name |
+----+------+------+
| 4 | 19.5 | diny |
+----+------+------+
1 row in set

Summarize

1. When we need to match data with regular expressions, we can use REGEXP and NOT REGEXP operators (similar to LIKE and NOT LIKE);

2. REGEXP is case-insensitive by default, but you can use the BINARY keyword to force case sensitivity; WHERE NAME REGEXP BINARY '^[AZ]';

3. REGEXP defaults to partial matching, that is, it returns true if there is a match. For example: SELECT 'A123' REGEXP BINARY '[AZ]' returns 1;

4. If you use () to match, the content inside the brackets will be matched as a whole. For example, (ABC) needs to match the entire ABC.

5. This is just an introduction to the basics of regular expressions. If you want a more thorough understanding, you can refer to the regular expression tutorial, which I think is well written.

This is the end of this article about the detailed explanation of regular matching for MySQL query. For more information about regular matching for MySQL query, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

How to use regular expression query in mysql
mysql regular expression query contains non-digits and characters records
How to use regular expression query in MySql
Detailed explanation of MySql basic query, join query, subquery, regular expression query
Summary of classic examples of MySQL query operations using regular expressions
MySql official manual study notes 2 MySql fuzzy query and regular expression

<<: Methods and steps for deploying GitLab environment based on Docker

>>: Details on using JS array methods some, every and find