Retrieving Information from a Table
209
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
[899]
and
NOT REGEXP
[899]
operators (or
RLIKE
[899]
and
NOT RLIKE
[899]
, which are synonyms).
The following list describes some characteristics of extended regular expressions:
• “
.
” 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
[899]
pattern match succeeds if the pattern matches anywhere in the value being tested.
(This differs from a
LIKE
[896]
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
[896]
queries shown previously
are rewritten here to use
REGEXP
[899]
.
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
[899]
comparison to be case sensitive, use the
BINARY
[948]
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.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
