MySQL Learning note Nine string pattern matching

When we use queries, we often encounter fuzzy conditional queries, and fuzzy queries involve string pattern matching.

Here, the main two: standard SQL pattern matching, extended regular expression pattern matching.

One, standard SQL pattern matching

The pattern matching of SQL allows you to match any single character with "_" , while "%" matches any number of characters (including 0 characters). In MySQL, the default mode of SQL is case-insensitive. Some examples are shown below. Note that in your

When using SQL mode, you cannot use = or! =, and use the like or not-like comparison operator.

To find a name that contains exactly 5 characters, use the "_" mode character, which you can write:

SELECT * FROM student WHERE name Like "_ _ _ _ _";(space is not, just for demonstration convenience)

"%" matches the same as Oracle, omitted here.

Second, extended regular expression pattern matching

Similar to the use of a Java or JavaScript regular expression.

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you test for this type of pattern, use the REGEXP and not REGEXP operators (or rlike and not rlike, which are

Synonyms).
Some of the characters that extend the 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, while "[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 types of writing. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of two notation.
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.

The following is a simple example:

To find the name beginning with "B", use "^" to match the beginning of the name and "[BB]" to match the lowercase or uppercase "B":
Mysql> SELECT * FROM student 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 student 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 that contains a "w", use "[WW]" to match lowercase or uppercase "W":
Mysql> SELECT * FROM student 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 |
+----------+-------+---------+------+------------+------------+
Now that if a regular expression appears anywhere in the value and its pattern matches, you don't have to put a wildcard in the two aspects of the pattern in the previous query so that it matches the entire value, just as if you were using a SQL schema.
To find a name that contains exactly 5 characters, use "^" and "$" to match the first and last names, and 5 "." The instance is between the two:
Mysql> SELECT * FROM student 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 student 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 |
+-------+--------+---------+------+------------+-------+