3.3.4.7 Pattern Matching


MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed. 


SQL pattern matching allows you to use `_' to match any single character and `%' to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case insensitive by default. Some examples are shown below. Note that you do not use = or != when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead. 


To find names beginning 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 ending with `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 containing a `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 names containing 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 for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms). 


Some characteristics of extended regular expressions are: 


`.' matches any single character. 
A character class `[...]' matches any character within the brackets. For example, `[abc]' matches `a', `b', or `c'. To name a range of characters, use a dash. `[a-z]' matches any lowercase letter, whereas `[0-9]' matches any digit. 
`*' matches zero or more instances of the thing preceding it. For example, `x*' matches any number of `x' characters, `[0-9]*' matches any number of digits, and `.*' matches any number of anything. 
Regular expressions are case sensitive, but you can use a character class to match both lettercases if you wish. For example, `[aA]' matches lowercase or uppercase `a' and `[a-zA-Z]' matches any letter in either case. 
The pattern matches if it occurs anywhere in the value being tested. (SQL patterns match only if they match the entire value.) 
To anchor a pattern so that it must match the beginning or end of the value being tested, use `^' at the beginning or `$' at the end of the pattern. 
To demonstrate how extended regular expressions work, the LIKE queries shown above are rewritten below to use REGEXP. 


To find names beginning 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 |
+--------+--------+---------+------+------------+------------+


Prior to MySQL Version 3.23.4, REGEXP is case sensitive, and the previous query will return no rows. To match either lowercase or uppercase `b', use this query instead: 


mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";


From MySQL 3.23.4 on, to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string. This query will match only lowercase `b' at the beginning of a name: 


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


To find names 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 names containing a lowercase or uppercase `w', use this 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       |
+----------+-------+---------+------+------------+------------+


Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wild card on either side of the pattern to get it to match the entire value like it would be if you used a SQL pattern. 


To find names containing exactly five characters, use `^' and `$' to match the beginning and end of the name, and five instances of `.' 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 could also write 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  |
+-------+--------+---------+------+------------+-------+