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 here. 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 five
instances of 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 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.
A REGEXP pattern match succeeds if the
pattern matches anywhere in the value being tested. (This
differs from a LIKE pattern match,
which succeeds only if the pattern matches 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 previously are rewritten
here 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 |
+--------+--------+---------+------+------------+------------+
If you really want to force a REGEXP
comparison to be case sensitive, use the
BINARY keyword to make one of the strings a
binary string. This query matches 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 ‘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 wildcard on either side of the pattern to get
it to match the entire value like it would be if you used an
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 |
+-------+--------+---------+------+------------+-------+
Appendix G, Regular Expressions, provides more information about the
syntax for regular expressions.
