(mysql.info.gz) Pattern matching
Info Catalog
(mysql.info.gz) Working with NULL
(mysql.info.gz) Retrieving data
(mysql.info.gz) Counting rows
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 succeed 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 |
+--------+--------+---------+------+------------+------------+
Prior to MySQL Version 3.23.4, `REGEXP' is case sensitive, and the
previous query will return no rows. In this case, 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, 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 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 `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 |
+-------+--------+---------+------+------------+-------+
Info Catalog
(mysql.info.gz) Working with NULL
(mysql.info.gz) Retrieving data
(mysql.info.gz) Counting rows
automatically generated byinfo2html