Detailed discussion on MYSQL pattern matching REGEXP and like code usage sharing

The following editor will bring you a commonplace talkMYSQLThe usage of pattern matching REGEXP and like. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

like

likeRequires that the entire data must match. REGEXP only requires partial matching.
In other words, to use Like, all the contents of this field must meet the conditions, while REGEXP only needs any one fragment to meet the conditions.

MySQL provides standard SQL pattern matching (like), as well as an extended format based on Unix utilities such as vi, grep, and sed Regular expressionsPattern matching format ( regexp).
SQL pattern matching allows you to use "_" to match any single character, and "%" to match any number of characters (including zero characters). In MySQL, the SQL mode defaults to ignoring case . Some examples are shown below. Note that when you use SQL mode, you cannot use = or !=; instead, use the LIKE or NOT LIKE comparison operator .

To find names starting with "b":

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with "fy":

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

To find names that contain a "w":

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

To find names that contain exactly 5 characters , use the "_" pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

REGEXP

Another type of matching is based on regular expressions. When you test for matches on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

"." matches any single character.

A character class "[...]" matches any character within square brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[a-z]" matches any lowercase letter, while "[0-9]" matches any number.
" * " matches zero or more of the things preceding it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of anything.

Regular expressions are case-sensitive, but if you wish, you can use a character class to match both writings. For example, "[aA]" matches a lowercase or uppercase "a" and "[a-zA-Z]" matches any letter written either way.

Patterns match if it appears anywhere in the value being tested (SQL patterns match as long as they match the entire value).
To position a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.
To illustrate how extended regular expressions work, the LIKE query shown above is rewritten below using REGEXP:
To find out what starts with "b" For names that begin with, use "^" to match the beginning of the name and "[bB]" to match a lowercase or uppercase "b":

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending in "fy" , use "$" to match the end of a name:

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

To find names that contain a "w", use "[wW]" to match a lowercase or uppercase "w":

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

[^......], matches characters not included in [], for example, querying for names starting with w/z/s

select name from 表名 where name regexp '^[^wzs]';

*, Repeat 0 or more times. Students who are familiar with javascript regularity know that

'str*' can match st/str/strr/strrr...

?, repeat 0 or 1 times

'str?' can match st/str

+, repeated 1 or more times

'str+' can match str/strr/strrr /strrrr......

Compared with the regular rules in JavaScript, the regular rules here are simplified versions. There is no lazy matching/greedy matching. \w\s\d is not supported in []. The grammar does not support Chinese, it is relatively simple.

The above is the detailed content of Detailed discussion on MYSQL pattern matching REGEXP and like code usage sharing. For more information, please follow other related articles on the PHP Chinese website!