Database Reference
In-Depth Information
▼
Output
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
▼
Analysis
The search pattern
'%anvil%'
means
match any value that contains the text
anvil
anywhere within it
,
regardless of any characters before or after that text.
Wildcards can also be used in the middle of a search pattern, although that is
rarely useful. The following example finds all products that begin with an
s
and
end with an
e
:
▼
Input
SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';
Tip
Searching For Partial Email Addresses There is one situation in which wildcards may
indeed be useful in the middle of a search pattern, and that is looking for email address-
es based on a partial address, such as
WHERE email LIKE 'b%@forta.com'
.
It is important to note that, in addition to matching one or more characters,
%
also matches zero characters.
%
represents zero, one, or more characters at the
specified location in the search pattern.
Note
Watch for Trailing Spaces Trailing spaces can interfere with wildcard matching. For
example, if any of the anvils had been saved with one or more spaces after the word
anvil, the clause
WHERE prod_name LIKE '%anvil'
would not have matched them
as there would have been additional characters after the final
l
. One simple solution to
this problem is to always append a final
%
to the search pattern. A better solution is to
trim the spaces using functions, as discussed in Chapter 11, “Using Data Manipulation
Functions.”
Caution
Watch for
NULL
While it may seem that the
%
wildcard matches anything, there is one
exception,
NULL
. Not even the clause
WHERE prod_name LIKE '%'
will match a row
with the value
NULL
as the product name.
