MySQL Fuzzy query

　　

MySQL provides standard SQL pattern matching and an extended regular expression based on Unix-like utilities such as VI, grep, and SED

pattern-matching format.

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 when you use SQL mode, you do not

can use = or! =, and use the like or not-like comparison operator.

In order to find the name starting with "B":

Mysql> select * FROM pet WHERE name "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 |
+--------+--------+---------+------+------------+------------+

In order to find the name ending with "FY":

Mysql> select * FROM pet WHERE name "%fy";
+--------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

In order to find the name containing a "W":

Mysql> select * FROM pet WHERE name "%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 is like "_____";
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you perform a match test on this type of pattern, use the

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
What number.
"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*"
Match any number of numbers, while ". *" 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 the
End With "$".
To illustrate how an extended regular expression works, the like query shown above uses RegExp rewrite below:

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 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 that contains a "w", use "[WW]" to match 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, its pattern matches, it will not have to be in the previous query in the pattern of two

Aspect to place a wildcard character 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 "." Instance in

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 |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

Finding numbers and other fuzzy query statements
SELECT * FROM pet where name REGEXP "[^a-za-z].";

MySQL Fuzzy query