What string matching patterns are there in MySQL
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.
