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

In this issue, the editor will bring you about the string matching patterns in MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

What are the MySQL string matching patterns

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 NOTLIKE comparison operator.

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

Mysql > SELECT*FROMpetWHEREnameLIKE "b"

Mysql > SELECT*FROMpetWHEREnameREGEXP "^ [bB]"

Mysql > SELECT*FROMpetWHEREnameREGEXP "fy___FCKpd___5quot

+-+ +

| | 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*FROMpetWHEREnameREGEXP "[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 |

+-+ +

What are the MySQL string matching patterns

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*FROMpetWHEREnameREGEXP "^. _ FCKpd___7quot

+-+ +

| | 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*FROMpetWHEREnameREGEXP "^. {5} _ _ FCKpd___8quot

+-+ +

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

+-+ +

| | Claws | Gwen | cat | m | 1994-03-17 | NULL |

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

+-+ +

These are the string matching patterns in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.