SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards
- by Pinal Dave
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 (http://blog.sqlauthority.com)
Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
