MySQL mode matching

MySQL provides standard SQL mode matching and a kind of Unix-like UtilityProgramFor example, the format of the extension Regular Expression Pattern Matching of VI, grep, and sed.

SQL mode 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. The following are some examples. Note that = or! =; Instead, use the like or not like comparison operator.

To find the name starting with "B:

 

Code
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 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 find out which exactly contains 5 characters, use the "_" mode 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 |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
Other types of pattern matching provided by MySQL use extended regular expressions. 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 ).

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 demonstrate how the extended regular expression works, use Regexp to rewrite the like Query shown above:

To find the name starting with "B", use "^" to match the start of the name:

Mysql> select * From Pet where name Regexp '^ B ';
+ -------- + --------- + ------ + ------------ +
| Name | Owner | species | sex | birth | death |
+ -------- + --------- + ------ + ------------ +
| Buffy | Harold | dog | f | 1989-05-13 | null |
| Boane | Diane | dog | M |
+ -------- + --------- + ------ + ------------ +
If you want to force Regexp to be case sensitive, use the binary keyword to convert a string to a binary string. This query only matches the lowercase 'B' of the first letter of the name '.

Mysql> select * From Pet where name Regexp binary '^ B ';
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 find a name containing "W, use the following query:

mysql> select * From Pet where name Regexp '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 |
+ ---------- + ------- + --------- + ------ + ------------ +
since a regular expression appears anywhere in the value, its pattern matches, you do not have to place a wildcard on both sides of the schema in the previous query to match the entire value, just as if you were using an SQL schema.

To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the range:

Mysql> select * From Pet where name Regexp '^... $ ';
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Name | Owner | species | sex | birth | death |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Claws | Gwen | cat | M | 1994-03-17 | null |
| Buffy | Harold | dog | f | 1989-05-13 | null |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
You can also use the "{n}" "Repeat n times" operator to overwrite the previous query:

Mysql> select * From Pet where name Regexp '^. {5} $ ';
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Name | Owner | species | sex | birth | death |
+ ------- + -------- + --------- + ------ + ------------ + ------- +
| Claws | Gwen | cat | M | 1994-03-17 | null |
| Buffy | Harold | dog | f | 1989-05-13 | null |
+ ------- + -------- + --------- + ------ + ------------ + ------- +