Shulou(Shulou.com)05/31 Report--

This article mainly introduces "string pattern matching example explanation in MySQL". In daily operation, I believe many people have doubts on string pattern matching example explanation in MySQL. Xiaobian consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the doubts of "string pattern matching example explanation in MySQL". Next, please follow the editor to study!

MySQL provides standard SQL pattern matching, as well as a format based on extended regular expression pattern matching like Unix utilities such as vi, grep, and sed.

Standard SQL pattern 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 mode of SQL ignores case by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; instead, use the LIKE or NOT LIKE comparison operator.

For example, in the table pet, to find names that start with "b":

> 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 out the name that ends 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 a 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 a name that contains 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 |

+-+ +

Extended regular expression pattern matching

Other types of pattern matching provided by MySQL are using extended regular expressions. When you test the matching of such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT

RLIKE, they are synonyms.

Some of the characters that extend regular expressions 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 "-".

"[amurz]" matches any lowercase letter, while "[0-9]" matches any number. "

"*" matches zero 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.

Anything you can measure.

Regular expressions are case-sensitive, but if you want, you can use one character class to match both. For example, "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]"

Any letter with two ways of writing.

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

To locate 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 using REGEXP below:

To find names that start with "b", use "^" to match the beginning of the 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 that ends 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 that contains a "w", use "[wW]" to match a 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 a regular expression appears anywhere in the value and its pattern matches, it is no longer necessary to place a wildcard on both sides of the pattern in the previous query to make it match the entire value

Just like if you use a SQL mode.

To find a name that contains exactly five characters, use "^" and "$" to match the beginning and end of the name, and 5 "." The instance is between the two:

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 using the "{n}"repeat n times" operator:

Mysql > SELECT * FROM pet WHERE name REGEXP "^. {5} $"

+-+ +

| | name | owner | species | sex | birth | death | |

At this point, on the "string pattern matching examples in MySQL to explain" the end of the study, I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.