SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.

Wildcard ranges

If you have ever been to a convention where they have a morning registration desk that must handle thousands of people in a short time you know they must put some pre-planning thought into how to handle this burst of volume. In fact often they will have many registration desks running in parallel to make things run faster. The first registration desk might handle all customer last names starting from A to K. Desk 2 will handle names from L to Q and the third desk will handle from R to Z.

With my last name being Morelan I would naturally head to registration desk #2 knowing that desk has the list with my name on it and the other desks don’t. Now let’s say you are in charge of creating these three separate lists and sending them out to the right registration workers. You know how to sort but how can you separate this lists using wildcards?

Wildcard Basics Recap

Lets start off with something most of us know already. Most SQL folks understand the usefulness and power of the basic uses of wildcards. Using wildcards allows you to do pattern matches in a column. In this case our criteria does not want to use the = sign to find a pattern match. The operator that allows you to do approximate predicates is LIKE. The LIKE operator allows you to do special relative searches to filter your result set.

--Find all LastNames that start with the letter A
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'

To find everyone whose last name starts with the letter B, you need “B” to be the first letter. After the letter B you can have any number of characters. Using B% in single quotes after the LIKE operator gets all last names starting with the letter B.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'B%'


Wildcard ranges or set specifiers

If you want to find all LastName values starting with the letters A or B you can use two predicates in your WHERE clause. You need to separate them with the OR operator.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'

Finding names beginning with A or B is easy. How about the registration desk example where want the names ranging from A-K? This works well until you want a range of A-K as in the example below:

--Find all LastNames ranging from A-K
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
OR LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'E%'
OR LastName LIKE 'F%'
OR LastName LIKE 'G%'
OR LastName LIKE 'H%'
OR LastName LIKE 'I%'
OR LastName LIKE 'J%'
OR LastName LIKE 'K%'


The previous query does find LastName values starting from A-K. However, if you need a range of letters, the LIKE operator has many better options. We only really care about the first letter of the last name and there a several first letters that fit with what were looking for. The first letter of the last name can be A,B,C,D,E,F,G,H,I,J or K. Simply list all the choices you want for the first letter inside a set of square brackets.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[ABCDEFGHIJK]%'


Square brackets with wildcards enclose ranges or sets for 1 position. In this case the first position is a set of 11 different possible letters. This is not a series of letter but a multiple choice of letters. For example this works regardless of the order you put your letters in. This code sample below does the exact same thing.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[KBCDEFGHIJA]%'


Again the set is how many letters you put in the square brackets. The code below is a logical mistake where you won’t get A to K but you just get A or K for the first letter.

--Find all LastNames starting with A or K (Mistake
SELECT *
FROM Employee
WHERE LastName LIKE '[AK]%'


Since we’re looking for the first letter to be within a range  from A to K, we specify that range in square brackets. This is even easier than using a set. The wildcard after the brackets allows any number of characters after the range.

--LastNames ranging from A to K using a range
SELECT *
FROM Employee
WHERE LastName LIKE '[A-K]%'


Note: this range will not work if your LIKE was changed to an equal (=) sign. The following code will not return any records to your result set:

--Bad query (it won’t error but returns no records)
SELECT *
FROM Employee
WHERE LastName = '[A-K]%'


Note: If you want to setup the sample JProCo database on your system you can watch this video.

Question 1

Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

1. SELECT * FROM Customer
   WHERE Firstname <= ‘m%’
2. SELECT * FROM Customer
   WHERE Firstname  = ‘a-m%’
3. SELECT * FROM Customer
   WHERE Firstname like ‘a-m%’
4. SELECT * FROM Customer
   WHERE Firstname  = ‘[a-m]% ‘
5. SELECT * FROM Customer
   WHERE Firstname like ‘[a-m]%’

Please post your answer in comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)