SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

SQL SERVER - Puzzle #1 - Querying Pattern Ranges and Wild Cards SQLQueries2012Vol1-9 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.

SQL SERVER - Puzzle #1 - Querying Pattern Ranges and Wild Cards j2pcontest-613-1

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.

SQL SERVER - Puzzle #1 - Querying Pattern Ranges and Wild Cards j2pcontest-613-2

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)

Previous Post
SQL SERVER – New SQL Server 2012 Functions – Webinar by Rick Morelan
Next Post
SQL – Step by Step Guide to Download and Install NuoDB – Getting Started with NuoDB

Related Posts

152 Comments. Leave new

  • abhishek mishra
    June 13, 2013 6:57 pm

    Option d and f both give desired result

    Reply
  • Option (F):
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Reply
  • Still find d and f to be exactly the same , the same with e and g.
    So My answer is b

    Reply
  • Hi Pinal,
    As of now (June 14) Options b&c, d&f and e&g are still the same. But anyways my answer is SELECT * FROM Customer WHERE FirstName LIKE ‘[a-m]%z’
    :)

    Reply
  • Kshitij Chawrey
    June 14, 2013 12:10 pm

    Option “d” and “f” are the ones who will return the expected result.

    Reply
  • Option ‘d’ and ‘f’ are similar. Will work.

    Reply
  • Gourav Chavan
    June 14, 2013 3:34 pm

    option d and f are identical and the correct one’s too

    Reply
  • B Jagan Mohan Rao
    June 14, 2013 4:05 pm

    g) is the answer

    Reply
  • Sreekanth K S
    June 14, 2013 6:56 pm

    d. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    This should do the trick
    Regards,
    Sreekanth

    Reply
  • Great contest! My answer(s), based on the way the choices appear on the blog on 6/14, is D & F — (there appear to be several sets of duplicate responses to choose from (d&f), (b&c), (e*g) – perhaps unintentional?

    Reply
  • SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%[z]’

    Reply
  • Hi Pinal,
    There is a typo error. As option b & c are same, d & f are same and also e & g. So, I believe there are only three options available for this puzzle.
    You have asked for the name starts with letters and not the range through, so the right answer is option b & c.

    SELECT * FROM Customer
    WHERE FirstName LIKE ‘a-m%z’

    Regards,
    Hitesh Shah

    Reply
  • Partha Dutta Gupta
    June 17, 2013 5:12 pm

    d command will work

    Reply
  • d and f are correct answers.

    Reply
  • d and f are the same and right:
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Reply
  • d. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’
    f. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’
    both are same. So d & f are right answer…

    Reply
  • option d & f

    Reply
  • I tried with following query in Oracle db, But it is not working

    select name from employee where name like ‘[a-v]%’, Not sure what is wrong?

    Reply

Leave a Reply

Menu