Database Reference
In-Depth Information
Pattern Matching
Pattern matching
is a technique that determines whether a specific character string matches a specified
pattern. A pattern can be created by using a combination of regular characters and wildcard characters.
During pattern matching, regular characters must exactly match as specified in the character string.
LIKE
and
NOT LIKE
(negation) are the operators are used for pattern matching. Remember that pattern
matching is case-sensitive. SQL Server supports the following wildcard characters for pattern matching:
•
% (percent mark)
:
This wildcard represents zero to many characters. For example,
WHERE title LIKE '%C# 5.0%'
finds all book titles containing the text
C# 5.0
,
regardless of where in the title that text occurs—at the beginning, middle, or end.
In this case, book titles such as
C# 5.0: An Introduction
,
Accelerated C#5.0
, and
Beginning C# 5.0 Databases
will be listed.
•
_ (underscore)
:
A single underscore represents any single character. By using this
wildcard character, you can be very specific in your search about the character
length of the data you seek. For example,
WHERE au_fname LIKE '_ean'
finds all the
first names that consist of four letters and that end with
ean
(Dean, Sean, and so
on).
WHERE aufname LIKE 'a____n'
finds all the first names that begin with
a
and
end with
n
and have any other three characters in between, for example, allan,
amman, aryan, and so on.
•
[] (square brackets)
:
These specify any single character within the specified range,
such as
[a-f]
, or a set, such as
[abcdef]
or even
[adf]
. For example,
WHERE
aulname LIKE '[C-K]arsen'
finds author last names ending with
arsen
and
starting with any single character between
C
and
K
, such as Carsen, Darsen,
Larsen, Karsen, and so on.
•
[^] (square brackets and caret)
: These specify any single character not within the
specified range, such as
[^a-f]
, or a set, such as
[^abcdef]
. For example,
WHERE
au_lname LIKE 'de[^]%'
retrieves all author last names starting with
de
, but the
following letter cannot be
l
.
Try It: Using the Percent (%) Character
To see how the
%
wildcard character works, open a New Query window in SQL Server Management
Studio Express. Enter the following query, and click Execute. You should see the results shown in
Figure 5-8.
Select AddressID, AddressLine1, City
from Person.Address
where City like 'R%'
