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:
There are several different wildcard expressions supported by Transact SQL:
SELECT * FROM employees WHERE last_name LIKE 'C%'
- 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.
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 '[a-m]%[^aeiou]'
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.
SELECT * FROM employees WHERE last_name LIKE '____'