4.7 Pattern Matching with SQL Patterns
4.7.1 Problem
You want to perform a pattern match
rather than a literal comparison.
4.7.2 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.
4.7.3 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 substring anywhere: mysql> SELECT name FROM metal WHERE name LIKE '%er%';
+---------+
| name |
+---------+
| copper |
| mercury |
| silver |
+---------+
Strings that contain a substring at a specific position (the pattern
matches only if pp occurs at the third position of
the name column):
mysql> SELECT name FROM metal where name LIKE '_ _pp%';
+--------+
| name |
+--------+
| copper |
+--------+
A SQL pattern matches successfully only if it matches the entire
comparison value. Thus, of the following two pattern matches, only
the second succeeds:
'abc' LIKE 'b'
'abc' LIKE '%b%'
To reverse the sense of a pattern match, use NOT
LIKE. The following query finds strings that
contain no i characters:
mysql> SELECT name FROM metal WHERE name NOT LIKE '%i%';
+---------+
| name |
+---------+
| copper |
| gold |
| lead |
| mercury |
+---------+
SQL patterns do not match
NULL values. This is true both for
LIKE and NOT
LIKE:
mysql> SELECT NULL LIKE '%', NULL NOT LIKE '%';
+---------------+-------------------+
| NULL LIKE '%' | NULL NOT LIKE '%' |
+---------------+-------------------+
| NULL | NULL |
+---------------+-------------------+
In some cases, pattern matches are equivalent to substring
comparisons. For example, using patterns to find strings at one end
or the other of a string is like using LEFT( ) or
RIGHT( ):
|
str LIKE 'abc%'
|
LEFT(str,3) = 'abc'
|
str LIKE '%abc'
|
RIGHT(str,3) = 'abc'
|
If you're matching against a column that is indexed
and you have a choice of using a pattern or an equivalent
LEFT( ) expression, you'll likely
find that the pattern match is faster. MySQL can use the index to
narrow the search for a pattern that begins with a literal string;
with LEFT( ), it cannot.
Unlike some other databases, MySQL
allows pattern matches to be applied to numeric or date values, which
can sometimes be useful. The following table shows some ways to test
a DATE value d using function
calls that extract date parts and using the equivalent pattern
matches. The pairs of expressions are true for dates occurring in the
year 1976, in the month of April, or on the first day of the month:
|
YEAR(d) = 1976
|
d LIKE '1976-%'
|
MONTH(d) = 4
|
d LIKE '%-04-%'
|
DAYOFMONTH(d) = 1
|
d LIKE '%-01'
|
|
| 
No comments:
Post a Comment