Querying Pattern Ranges and Wild Cards

http://blog.sqlauthority.com/2013/06/11/sql-server-puzzle-1-querying-pattern-ranges-and-wild-cards/#comment-497480

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 EmployeeWHERE 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 EmployeeWHERE 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 EmployeeWHERE 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 CustomerWHERE FirstName LIKE 'm%z'

b. SELECT * FROM CustomerWHERE FirstName LIKE 'a-m%z'

c. SELECT * FROM CustomerWHERE FirstName LIKE 'a-m%z'

d. SELECT * FROM CustomerWHERE FirstName LIKE '[a-m]%z'

e. SELECT * FROM CustomerWHERE FirstName LIKE '[a-m]z%'

f. SELECT * FROM CustomerWHERE FirstName LIKE '[a-m]%z'

g. SELECT * FROM CustomerWHERE FirstName LIKE '[a-m]z%'

Correct answer is D and F