Wildcard ranges
If you have ever been to a convention
where they have a morning registration desk that must handle thousands
of people in a short time you know they must put some pre-planning
thought into how to handle this burst of volume. In fact often they will
have many registration desks running in parallel to make things run
faster. The first registration desk might handle all customer last names
starting from A to K. Desk 2 will handle names from L to Q and the
third desk will handle from R to Z.
With my last name being Morelan I would
naturally head to registration desk #2 knowing that desk has the list
with my name on it and the other desks don’t. Now let’s say you are in
charge of creating these three separate lists and sending them out to
the right registration workers. You know how to sort but how can you
separate this lists using wildcards?
Wildcard Basics Recap
Lets start off with something most of us
know already. Most SQL folks understand the usefulness and power of the
basic uses of wildcards. Using wildcards allows you to do pattern
matches in a column. In this case our criteria does not want to use the =
sign to find a pattern match. The operator that allows you to do
approximate predicates is LIKE. The LIKE operator allows you to do special relative searches to filter your result set.
--Find all LastNames that start with the letter A
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
To find everyone whose last name starts
with the letter B, you need “B” to be the first letter. After the letter
B you can have any number of characters. Using B% in single quotes
after the LIKE operator gets all last names starting with the letter B.
--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'B%'
Wildcard ranges or set specifiers
If you want to find all LastName values
starting with the letters A or B you can use two predicates in your
WHERE clause. You need to separate them with the OR operator.
--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
Finding names beginning with A or B is
easy. How about the registration desk example where want the names
ranging from A-K? This works well until you want a range of A-K as in
the example below:
--Find all LastNames ranging from A-K
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
OR LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'E%'
OR LastName LIKE 'F%'
OR LastName LIKE 'G%'
OR LastName LIKE 'H%'
OR LastName LIKE 'I%'
OR LastName LIKE 'J%'
OR LastName LIKE 'K%'
The previous query does find LastName
values starting from A-K. However, if you need a range of letters, the
LIKE operator has many better options. We only really care about the
first letter of the last name and there a several first letters that fit
with what were looking for. The first letter of the last name can be
A,B,C,D,E,F,G,H,I,J or K. Simply list all the choices you want for the
first letter inside a set of square brackets.
--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[ABCDEFGHIJK]%'
Square brackets with wildcards enclose
ranges or sets for 1 position. In this case the first position is a set
of 11 different possible letters. This is not a series of letter but a
multiple choice of letters. For example this works regardless of the
order you put your letters in. This code sample below does the exact
same thing.
--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[KBCDEFGHIJA]%'
Again the set is how many letters you put
in the square brackets. The code below is a logical mistake where you
won’t get A to K but you just get A or K for the first letter.
--Find all LastNames starting with A or K (Mistake
SELECT *
FROM Employee
WHERE LastName LIKE '[AK]%'
Since we’re looking for the first letter
to be within a range from A to K, we specify that range in square
brackets. This is even easier than using a set. The wildcard after the
brackets allows any number of characters after the range.
--LastNames ranging from A to K using a range
SELECT *
FROM Employee
WHERE LastName LIKE '[A-K]%'
Note: this range will not work
if your LIKE was changed to an equal (=) sign. The following code will
not return any records to your result set:
--Bad query (it won’t error but returns no records)
SELECT *
FROM Employee
WHERE LastName = '[A-K]%'
Posts
