Pattern Matching with SQL Patterns
Problem
You want to perform a pattern match rather than 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 Pattern Matching with Regular Expressions.
Discussion
Patterns are strings that contain special characters. These are 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 uses different operators and different sets of metacharacters. This section describes SQL patterns. Pattern Matching with Regular Expressions describes regular expressions.
The example here uses a table named metal
that contains the following
rows:
+----------+ | name | +----------+ | copper | | gold | | iron | | lead | | mercury | | platinum | | silver | | 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 metacharacters: _
matches
any single character, and %
matches
any sequence of characters, including 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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.