GNU Info

(mysql.info)Pattern matching

---

Next: Counting rows Prev: Working with NULL Up: Retrieving data

---

Enter node , (file) or (file)node

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  |
     +-------+--------+---------+------+------------+-------+