MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 enables 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. 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).
The following list describes some characteristics of extended regular expressions:
.
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 regular expression 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 |
+--------+--------+---------+------+------------+------------+
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:
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 as would be true with 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
{
(“repeat-n
}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 |
+-------+--------+---------+------+------------+-------+
For more information about the syntax for regular expressions, see Section 12.8.2, “Regular Expressions”.