Movatterモバイル変換

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 asvi,grep, andsed.

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 theLIKE orNOT LIKE comparison operators instead.

To find names beginning withb:

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 withfy:

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 aw:

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 theREGEXP andNOT REGEXP operators (orRLIKE andNOT RLIKE, which are synonyms).

The following list describes some characteristics of extended regular expressions:

To demonstrate how extended regular expressions work, theLIKE queries shown previously are rewritten here to useREGEXP.

To find names beginning withb, 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 aREGEXP comparison to be case-sensitive, use theBINARY keyword to make one of the strings a binary string. This query matches only lowercaseb at the beginning of a name:

SELECT * FROM pet WHERE name REGEXP BINARY '^b';

To find names ending withfy, 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 aw, 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{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  |+-------+--------+---------+------+------------+-------+

For more information about the syntax for regular expressions, seeSection 12.8.2, “Regular Expressions”.