Database Reference
In-Depth Information
5.8. Pattern Matching with SQL Patterns
Problem
You want to perform a pattern match, not a literal comparison.
Solution
Use the
LIKE
operator and an SQL pattern, described in this section. Or use a regular-
expression pattern match, described in
Recipe 5.9
.
Discussion
Patterns are strings that contain special characters known as metacharacters because
they stand for something other than themselves. MySQL provides two kinds of pattern
matching. One is based on SQL patterns and the other on regular expressions. SQL
patterns are more standard among different database systems, but regular expressions
are more powerful. The two kinds of pattern match use different operators and different
metacharacters. This section describes SQL patterns.
Recipe 5.9
describes regular ex‐
pressions.
The example here uses a table named
metal
that contains the following rows:
+----------+
| name |
+----------+
| gold |
| iron |
| lead |
| mercury |
| platinum |
| tin |
+----------+
SQL pattern matching uses the
LIKE
and
NOT
LIKE
operators rather than
=
and
<>
to
perform matching against a pattern string. Patterns may contain two special metachar‐
acters:
_
matches any single character, and
%
matches any sequence of characters, in‐
cluding the empty string. You can use these characters to create patterns that match a
variety of values:
• Strings that begin with a particular substring:
mysql>
SELECT name FROM metal WHERE name LIKE 'me%';
+---------+
| name |
+---------+
| mercury |
+---------+
