(mysql.info.gz) Pattern matching

 
 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