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

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)

About these ads

152 thoughts on “SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

  1. 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.

    Like

  2. 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.

    Like

  3. 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’

    Like

  4. 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';

    Like

  5. Hi, Puzzle #1 – Querying Pattern Ranges and Wild Cards

    Answer is option f

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

    ________________________________

    Like

  6. 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

    Like

  7. 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 :)

    Like

  8. Option B and C ,D and F ,E and G are same ….But i think nothing from above will work ..
    I would use..
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Like

  9. None of the options will produce the desired output, as the code used equal sign…

    we should use following code

    SELECT * FROM Customer
    WHERE FirstName like ‘[a-m]%z’

    Like

  10. All the above answers will not work as it uses = [equal to] operator.

    The correct answer is as below:
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Like

  11. None of the queries will provide desired result. Each query will return 0 rows. The correct answer is

    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[A-M]%Z’

    Like

  12. Hi Sir,

    All the queries has equal to sign hence none of the queries are going to work as explained in the 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.”

    Like

  13. Hi Pinal,

    None of the option is valid for the desired output. AS they will not return any values.

    This will surely work:
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[A-M]%Z’

    thanks,
    Kapil Singh

    Like

  14. I think you made a couple of typos :). Options d and f are the same and so are e and g. None of the options will work since they are not using the LIKE operator.

    Like

  15. Now I know, Everybody makes copy n paste mistakes :P
    Answer should be d or f since both are same.

    By the way, thanks for the post…Learnt something new :)

    Like

  16. Answer is d and f …

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

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

    d and f are same query.. :-)

    Like

  17. No any option will return desire result, Option ‘d’ and ‘f’ will return desire result Only if we remove the White Space from Like clause between first ” ‘ ” and “[“.

    Like

  18. Hi Pinal,

    Still d and f has the same sql and right answer is d & f both.
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    This sql will return as per expected result.

    Like

  19. Pinal,
    Answers are duplicate
    b is same as c
    d is same as f
    e is same as g

    I think you want to use ‘=’ in b,d,e & ‘LIKE’ in c,e,g options…

    Like

  20. After you fixed small typos in the options….
    The correct options for the above puzzle are: D & F

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

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

    Like

    • Hi chandrika I think suku is the correct answer because in question they asked all the first names. so i am also thinking select firstname from customer like this………All are worng even me

      Like

      • Hi Ram,
        May or May not….
        Actually I don’t think so…
        May be the Puzzle’s trick was =(Equal) operator against Pattern range only… (as per Puzzle tag line)

        Like

  21. I hope none of the above mentioned queries will give you the required result. The following modified query will do the needful.

    SELECT * FROM Customers
    WHERE CustomerName LIKE ‘[a-m]%z’

    Like

  22. Options “d” and “f” will give desire output.

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

    Like

  23. Option d and f, e and g looks the same. Anyways I prefer option ‘d’ i.e., (SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’)

    Like

  24. The correct options are: D and F

    SELECT *
    FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’ — logic behind this is first letter should be A, B, C, D, …., upto M and End Letter must be Z

    Returns all the customers who have first letter ( a or b or ….or m) and last letter Z in their FirstName

    Like

  25. both d and f options are same
    both of these will work and fulfill the requirement.
    d. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Like

  26. Option b equals c
    Option d equals f
    Option e equals g

    Answer is d or f :

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

    Like

  27. Hi Pinal,

    I can see both the options ‘d’ and ‘f’ are same.
    So, both options ‘d’ and ‘f’ will be the correct options here.
    SELECT * FROM Customer WHERE FirstName LIKE ‘[a-m]%z’

    However, more to the point query will be :
    SELECT FirstName FROM Customer WHERE FirstName LIKE ‘[a-m]%z’

    Like

  28. I see multiple letters with the same option. Either way, I think the correct code is:

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

    Like

  29. Pinal, Same issue as what t4rzsan has indicated some of them are identical! From what I can tell both d and f (which are identical) would be correct

    Like

  30. hi pinel dave
    D and F both answers are correct.

    because it starts with first character [a-m] any character in middle any characters beacause of % wild card the last character should be ‘Z’

    thanks,
    k.Rakesh

    Like

  31. Hi Pinal,
    The answer of your puzzel is d and f.because d and f are same.[a-m]% find those name which starts from a to m and after that last is z.

    Like

    • also answers d or f….or:
      SELECT distinct FirstName
      FROM Customer
      where FirstName LIKE ‘[a-m]%z‘
      ORDER BY FirstName

      Like

  32. Hi Pinal,
    Options d & f are same and both would return required results.
    d. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’
    f. SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    Regards,
    Deepak Khandelwal

    Like

  33. Answer is:
    SELECT * FROM Customer
    WHERE FirstName LIKE ‘[a-m]%z’

    But it is “d” and “f”… the same text :)

    Like

  34. 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’
    :)

    Like

  35. 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?

    Like

  36. 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

    Like

  37. 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’

    Like

  38. 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…

    Like

  39. 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s