Pattern Matching in SQL Server Queries

You may often need to create a SQL Server query that performs inexact pattern matching through the use of wildcard characters. The use of wildcards allows you to find data that fits a certain pattern, rather than specifying it exactly. For example, you can use the wildcard 'C%' to match any string beginning with a capital C.

To use a wildcard expression in a SQL query, you'll need to use the LIKE clause to specify it. For example, you could search for any employee in your database with a last name beginning with the letter C using the following Transact-SQL statement:

 SELECT *
 FROM employees
 WHERE last_name LIKE 'C%' 

There are several different wildcard expressions supported by Transact SQL:

• The % wildcard matches zero or more characters of any type. If you're familiar with DOS pattern matching, it's the equivalent of the * wildcard in that syntax.
• The _ wildcard matches exactly one character of any type. It's the equivalent of the ? wildcard in DOS pattern matching.
• You can specify a list of characters by enclosing them in square brackets. For example, the wildcard [aeiou] will match any vowel.
• You can specify a range of characters by enclosing the range in square brackets. For example, the wildcard [a-m] will match any letter in the first half of the alphabet.
• You can negate a range of characters by including the carat character immediately inside of the opening square bracket. For example, [^aeiou] matches any non-vowel character while [^a-m] matches any character not in the first half of the alphabet.

You may also combine these wildcards in complex patterns to perform more advanced queries. For example, suppose you needed to construct a list of all of your employees who have names that begin with a letter from the first half of the alphabet but do NOT end with a vowel. You could use the following query:

 SELECT *
 FROM employees
 WHERE last_name LIKE '[a-m]%[^aeiou]' 

Similarly, you could construct a list of all employees with last names consisting of exactly four characters by using the query:

 SELECT *
 FROM employees
 WHERE last_name LIKE '____' 

As you can tell, the use of SQL Server's pattern matching capabilities offers database users the ability to go beyond simple text queries and perform advanced searching operations.