Pattern Matching with SQL Patterns
Problem
You want to perform a pattern match rather than a literal comparison.
Solution
Use the LIKE operator and a SQL pattern, described in this
section. Or use a regular expression pattern match, described in
Recipe 4.8.
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; Recipe 4.8 describes regular expressions.
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 wide variety of values:
Strings that begin with a particular substring:
mysql>
SELECT name FROM metal WHERE name LIKE 'co%';+--------+ | name | +--------+ | copper | +--------+Strings that end with a particular substring:
mysql>
SELECT name FROM metal WHERE name LIKE '%er';+--------+ | name | +--------+ | copper | | silver | +--------+Strings that contain a particular ...