I often talk about the usage of MYSQL pattern matching REGEXP and like, mysqlregexp

I often talk about the usage of MYSQL pattern matching REGEXP and like, mysqlregexp

Like

LikeThe whole data must be matched, while REGEXP only needs to be partially matched.
That is to say, to use Like, all content of this field must meet the conditions, while REGEXP only needs to have any fragment to satisfy the conditions.

MySQL provides standard SQL mode matching (like) and a format (regexp) Based on Extended Regular Expression Pattern Matching Based on Unix utilities such as vi, grep, and sed ).
SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; And use the LIKE or not like comparison operator.

To find the name 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 |
| Boane | Diane | dog | m |
+ -------- + --------- + ------ + ------------ +

To find the name 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 the name containing "w:

Mysql> SELECT * FROM pet WHERE name LIKE "% w % ";
+ ---------- + ------- + --------- + ------ + ------------ +
| Name | owner | species | sex | birth | death |
+ ---------- + ------- + --------- + ------ + ------------ +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Boane | Diane | dog | m |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+ ---------- + ------- + --------- + ------ + ------------ +

To locate the name containing exactly five 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 matching method is based on a regular expression. When you perform a match test on this type of pattern, 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 in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.

Regular Expressions are case sensitive.But if you want to, you can use one character class to match two types of writing. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.

If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).
To locate a pattern so that it must match the start or end of the tested value,Use "^" at the beginning of the mode or "$" at the end of the mode ".
To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:
To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

Mysql> SELECT * FROM pet WHERE name REGEXP "^ [bB]";
+ -------- + --------- + ------ + ------------ +
| Name | owner | species | sex | birth | death |
+ -------- + --------- + ------ + ------------ +
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Boane | Diane | dog | m |
+ -------- + --------- + ------ + ------------ +

To find the name 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 locate the name containing a "w", use "[wW]" to match the "w" in lower case or upper case ":

Mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";
+ ---------- + ------- + --------- + ------ + ------------ +
| Name | owner | species | sex | birth | death |
+ ---------- + ------- + --------- + ------ + ------------ +
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Boane | Diane | dog | m |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+ ---------- + ------- + --------- + ------ + ------------ +

[^…], Match characters not included in [], for example, the name of a person except the start of w/z/s

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

* Repeated 0 or multiple times. Anyone familiar with javascript Regular Expressions knows that

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

?, Repeated 0 or 1 times

'Str? 'Can match st/str

+, Repeat once or multiple times

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

Compared with the regular expressions in javascript, the regular expressions here are simplified, and there is no inert/greedy match. The \ w \ s \ d syntax is not supported in [], and Chinese characters are not supported, relatively simple.

The usage of MYSQL pattern matching REGEXP and like in this article is all the content shared by Alibaba Cloud. I hope to give you a reference and support for the customer's house.