Pattern Matching in SQL Queries

SQL pattern matching allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.

Using the LIKE Operator

To use a wildcard expression in an SQL query, use the LIKE operator in a WHERE clause, and enclose the pattern within single quotation marks.

Using the % Wildcard to Perform a Simple Search

To search for any employee in your database with a last name beginning with the letter C, use the following Transact-SQL statement:

SELECT *
 FROM employees
 WHERE last_name LIKE 'C%' 

Omitting Patterns Using the NOT Keyword

Use the NOT keyword to select records that don't match the pattern. For example, this query returns all records whose name last does not begin with C:

SELECT *
 FROM employees
 WHERE last_name NOT LIKE 'C%' 

Matching a Pattern Anywhere Using the % Wildcard Twice

Use two instances of the % wildcard to match a particular pattern anywhere. This example returns all records that contain a C anywhere in the last name:

SELECT *
 FROM employees
 WHERE last_name LIKE '%C%' 

Finding a Pattern Match at a Specific Position

Use the _ wildcard to return data at a specific location. This example matches only if C occurs at the third position of the last name column:

SELECT *
 FROM employees
 WHERE last_name LIKE '_ _C%' 

Supported Wildcard Expressions in Transact SQL

There are several wildcard expressions supported by Transact SQL:

• The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. 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.
• Specify a list of characters by enclosing them in square brackets. For example, the wildcard [aeiou] matches any vowel.
• Specify a range of characters by enclosing the range in square brackets. For example, the wildcard [a-m] matches any letter in the first half of the alphabet.
• 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.

Combining Wildcards for Complex Patterns

Combine these wildcards in complex patterns to perform more advanced queries. For example, suppose you need 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 four instances of the _ pattern:

SELECT *
 FROM employees
 WHERE last_name LIKE '____' 

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