Database Reference
In-Depth Information
Solution
Use the
REGEXP
operator and a regular expression pattern, described in this section. Or
use an SQL pattern, described in
Recipe 5.8
.
Discussion
SQL patterns (see
Recipe 5.8
) are likely to be implemented by other database systems,
so they're reasonably portable beyond MySQL. On the other hand, they're somewhat
limited. For example, you can easily write an SQL pattern
%abc%
to find strings that
contain
abc
, but you cannot write a single SQL pattern to identify strings that contain
any of the characters
a
,
b
, or
c
. Nor can you match string content based on character
types such as letters or digits. For such operations, MySQL supports another type of
pattern-matching operation based on regular expressions and the
REGEXP
operator (or
NOT
REGEXP
to reverse the sense of the match).
REGEXP
matching uses the pattern ele‐
ments shown in the following table:
Pattern What the pattern matches
^
Beginning of string
$
End of string
.
Any single character
[...]
Any character listed between the square brackets
[^...]
Any character not listed between the square brackets
p1
|
p2
|
p3
Alternation; matches any of the patterns
p1
,
p2
, or
p3
*
Zero or more instances of preceding element
One or more instances of preceding element
+
n
instances of preceding element
{
n
}
m
through
n
instances of preceding element
{
m
,
n
}
You may already be familiar with these regular expression pattern characters; many of
them are the same as those used by
vi
,
grep
,
sed
, and other Unix utilities that support
regular expressions. Most of them are used also in the regular expressions understood
by programming languages. (For discussion of pattern matching in programs for data
validation and transformation, see
Chapter 12
.)
Recipe 5.8
shows how to use SQL patterns to match substrings at the beginning or end
of a string, or at an arbitrary or specific position within a string. You can do the same
things with regular expressions:
• Strings that begin with a particular substring:
mysql>
SELECT name FROM metal WHERE name REGEXP '^me';
+---------+
| name |
