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

User Comments

You can also store pattern in you database fields and use syntax like this:

SELECT country FROM ip_list WHERE '127.0.0.1' like ip

where ip is the field name with patterns.

Suppose i have the table:

+-------+--------+---------+------+
| name | owner | species | ID |
+-------+--------+---------+------+
| Claws | d\'a | cat | 1 |
| Buffy | Harold | dog | 2 |
+-------+--------+---------+------+

how can i find the first record?

I tried the following query, but it doesn't work

SELECT ID FROM mytable WHERE owner LIKE '%d\\\'a%'

How can i find it?

Anonymous user try this!
SELECT id FROM mytable WHERE owner REGEXP "^d...a$";

For more details refer the above pages!

I would be awesome if I had access to the value returned by the enclosed subexpression. For example:

mysql> SELECT "jklse89oli" REGEXP "^([0-9])+$"; -> 89

Add your own comment.