(mysql.info) pattern-matching

Info Catalog

(mysql.info) working-with-null

(mysql.info) retrieving-data

(mysql.info) 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 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 |
      +--------+--------+---------+------+------------+------------+
 
 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 matches 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  |
      +-------+--------+---------+------+------------+-------+
 
  regexp, provides more information about the syntax for regular
 expressions.
 

Info Catalog

(mysql.info) working-with-null

(mysql.info) retrieving-data

(mysql.info) counting-rows