MySQL string pattern matching extended regular expression pattern matching _mysql

Standard SQL pattern matching

SQL pattern matching allows you to match any single character with "_", while "%" matches any number of characters (including 0 characters). In MySQL, the SQL schema defaults to ignoring the case. Some examples are shown below. Note that when you use SQL mode, you cannot use = or!= and use like or not to compare operators.

For example, in table pet, to find a name that begins 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 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 that contains 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 "_" 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 |

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

MySQL provides standard SQL pattern matching and a format for extending regular expression pattern matching based on Unix utilities like VI, grep, and sed.

Extended Regular expression pattern matching

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you match this pattern to a test, 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 within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-".

"[A-z]" matches any lowercase letter, and "[0-9]" matches any number.

"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything.

Regular expressions are case-sensitive, but if you want to, you can use a character class to match two ways of writing. For example, "[AA]" matches either lowercase or uppercase "a" and "[A-za-z]" matches any of the letters in both ways.

If it appears anywhere in the tested value, the pattern matches (as long as they match the entire value and the SQL pattern matches).

To locate a pattern so that it must match the start or end of the tested value, use "^" at the beginning of the pattern or at the end of the pattern with "$".

To illustrate how extended regular expressions work, the like query shown below uses RegExp overrides:

To find the name that begins with "B", use "^" to match the start of the first name and "[BB]" to match 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 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 the name containing a "W", use "[WW]" to match the 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 |

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

Since if a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard character in the previous query on both sides of the pattern so that it matches the entire value.

Just as if you were using an SQL model.

To find names containing exactly 5 characters, use "^" and "$" to match the start and end of the first name, and the 5 "." Instances in between:

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 rewrite the previous query by using the "{n}" "Repeat N-times" operator:

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 |

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

Summarize

This article describes the knowledge about string pattern matching. Standard SQL pattern matching is the standard for SQL language and can be accepted by other relational database systems. Extending regular expression pattern matching

is developed according to the standards of UNIX systems and is generally only available on MySQL, but its functionality is stronger than the standard SQL pattern.