MySQL string mode matching extended regular expression mode matching

Standard SQL mode matching

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.

For example, in the pet table, 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 |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

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.

Extended Regular Expression Pattern Matching

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.

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 matching method. 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 start of the pattern or "$" at the end of the pattern ".

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 ___ fckpd ___ 5 quot ;;

+ -------- + --------- + ------ + ------------ + ------- +

| 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 |

+ ---------- + ------- + --------- + ------ + ------------ +

Since a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard in the two aspects of the pattern in the previous query so that it matches the entire value,

Just like if you use an SQL mode.

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 "^... ___ fckpd ___ 7 quot ;;

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| 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 rewrite the previous query:

Mysql> select * From Pet where name Regexp "^. {5} ___ fckpd ___ 8 quot ;;

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| Name | Owner | species | sex | birth | death |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| Claws | Gwen | cat | M | 1994-03-17 | null |

| Buffy | Harold | dog | f | 1989-05-13 | null |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

Summary

This section describes the knowledge about string mode matching. Standard SQL mode matching is the standard of SQL language and can be accepted by other relational database systems. Extended Regular Expression Pattern Matching

It is developed according to the standard of the UNIX system. Generally, it can only be used on MySQL, but its function is stronger than that of the standard SQL mode.