Note: Read at the end of the blog post how you can get five Joes 2 Pros Book #1 and a surprise gift.
I have been blogging for almost 7 years and every other day I receive questions about Querying Pattern Ranges. The most common way to solve the problem is to use Wild Cards. However, not everyone knows how to use wild card properly.
SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners Book On Amazon | Book On Flipkart
Learn SQL Server get all the five parts combo kit Kit on Amazon | Kit on Flipkart
Many people know wildcards are great for finding patterns in character data. There are also some special sequences with wildcards that can give you even more power. This series from SQL Queries 2012 Joes 2 Pros® Volume 1 will show you some of these cool tricks.
All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter2.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”
Querying Pattern Ranges
The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below:
SELECT *
FROM Employee
WHERE FirstName LIKE '%A'
To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement.
Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’:
SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'
The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below:
SELECT *
FROM Employee
WHERE FirstName LIKE '[ABCDEFGHIJK]%'
A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range.
Note: A predicate that uses a range will not work with the ‘=’ operator (equals sign). It will neither raise an error, nor produce a result set.
--Bad query (will not error or return any records)
SELECT *
FROM Employee
WHERE FirstName = '[A-K]%'Question: You want to find all first names that start with the letters A-M in your Customer table and end with the letter Z. Which SQL code would you use?
a. SELECT * FROM Customer
WHERE FirstName LIKE 'm%z'
b. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'
c. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'
d. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'
e. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'
f. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'
g. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'
Contest
- Leave a valid answer before June 18, 2013 in the comment section.
- 5 winners will be selected from all the valid answers and will receive Joes 2 Pros Book #1.
- 1 Lucky person will get a surprise gift from Joes 2 Pros.
- The contest is open for all the countries where Amazon ships the book (USA, UK, Canada, India and many others).
Special Note: Read all the options before you provide valid answer as there is a small trick hidden in answers.
Reference: Pinal Dave (https://blog.sqlauthority.com)
152 Comments. Leave new
Hi Pinal,
Answer is D
SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’
the answer is d. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’ because it will select with in range a-m and % can be any letters and finally ends with z.
d. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’
d. SELECT * FROM Customer
WHERE FirstName like ‘[a-m]%z’
None from list. I would use something like this
SELECT FirstName FROM Customer
WHERE FirstName like ‘[a-m]%z’
There are no one correct answers. The correct query is:
SELECT *
FROM Customer
WHERE FirstName LIKE ‘[a-m]%z’;
I believe you said that ‘A predicate that uses a range will not work with the ‘=’ operator ‘ therefore I assume none of the answers would work.
Option D and F
Hmm I’d say none of the above since all have = operator instead of LIKE operator. Also if using case sensitive collation without explicit cast using lower case letters in expression will not find upper case column values.
SELECT * FROM Customer WHERE FirstName LIKE ‘[A-M]%Z’ should do it.
Correct Answer is:
SELECT * FROM Customer WHERE FirstName LIKE ‘[a-m]%z’
There are duplicate given options for Answers. (B &C), (D&F), (E&G)
SELECT * FROM Customer
WHERE FirstName = ‘m%z’
b. SELECT * FROM Customer
WHERE FirstName = ‘a-m%z’
c. SELECT * FROM Customer
WHERE FirstName = ‘a-m%z’
d. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’
e. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]z%’
f. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’
g. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]z%’
Hi Pinal,
None of the above options give correct result. Because e&g options are same and d&f options are same… (You forgot to mention LIKE operator instead of = )
I think the contest answers are duplicated and doesn’t have correct answer in the given options….
The correct answer for the Contest is as follows:
SELECT * FROM Customer
WHERE FirstName LIKE ‘[a-m]%z’
none of the answers are correct : you want like with upper case and the examples are = with lower case
so I suggest : SELECT * FROM Customer
WHERE FirstName LIKE ‘[A-M]%Z’;
Hi, Puzzle #1 – Querying Pattern Ranges and Wild Cards
Answer is option f
f. SELECT * FROM Customer WHERE FirstName = ‘[a-m]%z’
________________________________
Non of them, because the operator is = , not LIKE
SELECT * FROM Customer
WHERE FirstName like ‘[a-m]%z’
option d
f. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]%z’
g. SELECT * FROM Customer
WHERE FirstName = ‘[a-m]z%’
sir here we need “Like” instead of “=” i think. because “=” dose not give any result me. and also not give any error also
There is no correct answer .. the correct answer is
SELECT * FROM Customer WHERE FirstName like ‘[a-m]%z’
Okay..Hope i dont sound stupid here….
but none of the above queries work since the % operator wont work with ‘=’ operator.
We have to use the like keyword for the proper output :)