[T-SQL] Wildcard

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 ASELECT *FROM EmployeeWHERE 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 BSELECT *FROM EmployeeWHERE 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 BSELECT *FROM EmployeeWHERE 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-KSELECT *FROM EmployeeWHERE 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 lettersSELECT *FROM EmployeeWHERE 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 lettersSELECT *FROM EmployeeWHERE 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 (MistakeSELECT *FROM EmployeeWHERE 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 rangeSELECT *FROM EmployeeWHERE 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 EmployeeWHERE LastName = '[A-K]%'