MySql official handbook study note 2 MySql's fuzzy queries and regular expressions

SQL pattern matching allows you to match any single character with "_" and any number of characters (including zero) with "%". In MySQL, the schema of SQL is case-insensitive by default. Here are some examples. Note that when using SQL mode, you cannot use = or! =; Instead, use the LIKE or NOT LIKE comparison operators.

To find names that start with "b" :

mysql > 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 names that end in "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 names that contain "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 five characters, use the "_" pattern 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 |

+-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL USES extended regular expressions. When you test this type of schema for matching, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The characters that extend the regular expression are:

· '.' matches any single character.

· character class "[...] "Matches any character in square brackets. For example, "[abc]" matches "a", "b", or "c". To name the range of characters, use a "-". "[a-z]" matches any letter, and "[0-9]" matches any number.

· "*" matches zero or more characters before it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of Numbers, and ".* "matches any number of any characters.

If the REGEXP pattern matches anywhere in the value being tested, the pattern matches (unlike the LIKE pattern, which matches only the entire value). 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 show how extended regular expressions work, rewrite the LIKE query shown above using REGEXP:

To find names that start with "b," use "^" to match the beginning of the name:

mysql > SELECT * FROM pet WHERE name REGEXP '^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 |

+--------+--------+---------+------+------------+------------+

If you want to force REGEXP to be case-sensitive, use the BINARY keyword to make one of the strings a 2-base string. This query matches only lowercase 'b' with the first letter of the name.

mysql > SELECT * FROM pet WHERE name REGEXP BINARY '^b';

To find names ending in "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 1 "w", use the following query:

mysql > SELECT * FROM pet WHERE name REGEXP '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 |

+----------+-------+---------+------+------------+------------+

Since a regular expression appears anywhere in the value and its pattern matches, you don't have to place a wildcard on either side of the pattern in the previous query to make it match the entire value, as you did with an SQL pattern.

To find a name that contains exactly five characters, use the words "^" and "$" to match the beginning and end of the name, with five". "instances in between:

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 override the previous query using the "{n}" and "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 |

+-------+--------+---------+------+------------+-------+