Internet, Networking, & Security Web Development 53 53 people found this article helpful Pattern Matching in SQL Queries Using wildcards for inexact matching By Mike Chapple Writer Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. our editorial process Twitter Mike Chapple Updated November 11, 2019 Web Development SQL CSS & HTML Web Design Tweet Share Email 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. Kate Ter Haar / Flickr/CC by 2.0 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.