Commonplace talk about the usage of MYSQL pattern matching REGEXP and like

like

LIKE requires the entire data to match, while REGEXP only requires a partial match.
That is to say, to use Like, all the contents of the field must meet the condition, while REGEXP only requires any fragment to meet the condition.

MySQL provides standard SQL pattern matching (like), as well as a format based on extended regular expression pattern matching similar to that used by Unix utilities such as vi, grep, and sed (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, SQL modes are case-insensitive by default . Some examples are shown below. Note that when you use SQL mode, you cannot use = or !=; use the LIKE or NOT LIKE comparison operators instead.

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 containing 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 containing 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 kind of matching is based on regular expressions. When you test for a match 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 the square brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[az]" matches any lowercase letter, and "[0-9]" matches any digit.
" * " matches zero or more of what precedes 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. For example, "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any letter in either case.

The pattern matches 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 names that begin with "b", use "^" to match the start 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 with "fy", use "$" to match the end of the 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 containing a "w", use "[wW]" to match either 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 contained in [], such as searching for names starting with w/z/s

select name from table name where name regexp '^[^wzs]';

*, repeated 0 or more times, students familiar with javascript regular expressions know

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

?, repeated 0 or 1 times

'str?' can match st/str

+, repeat 1 or more times

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

Compared with the regular expression in JavaScript, the regular expression here is a simplified version. There is no lazy matching/greedy matching. The syntax of \w\s\d is not supported in [], and Chinese is not supported. It is relatively simple .

The above article about the usage of MYSQL pattern matching REGEXP and like is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.