Pattern Matching in Search Conditions
The LIKE keyword searches for character string, date, or time values that match a specified pattern. For more information, see Data Types (Transact-SQL). The LIKE keyword uses a regular expression to contain the pattern that the values are matched against. The pattern contains the character string to search for, which can contain any combination of four wildcards.
Any string of zero or more characters.
Any single character.
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Enclose the wildcards and the character string in single quotation marks, for example:
- LIKE 'Mc%' searches for all strings that begin with the letters Mc (McBadden).
- LIKE '%inger' searches for all strings that end with the letters inger (Ringer, Stringer).
- LIKE '%en%' searches for all strings that contain the letters en anywhere in the string (Bennet, Green, McBadden).
- LIKE '_heryl' searches for all six-letter names ending with the letters heryl (Cheryl, Sheryl).
- LIKE '[CK]ars[eo]n' searches for Carsen, Karsen, Carson, and Karson (Carson).
- LIKE '[M-Z]inger' searches for all names ending with the letters inger that begin with any single letter from M through Z (Ringer).
- LIKE 'M[^c]%' searches for all names beginning with the letter M that do not have the letter c as the second letter (MacFeather).
The following query finds all phone numbers in the
Contact table that have area code
SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone LIKE '415%'
You can use
NOT LIKE with the same wildcards. To find all phone numbers in the
Contact table that have area codes other than
415, use either of these equivalent queries:
SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone NOT LIKE '415%' -- Or SELECT Phone FROM AdventureWorks.Person.Contact WHERE NOT Phone LIKE '415%'
IS NOT NULL clause can be used with wildcards and the
LIKE clause. For example, the following query retrieves telephone numbers from the
Contact table in which the telephone number begins with
IS NOT NULL:
USE AdventureWorks; GO SELECT Phone FROM Person.Contact WHERE Phone LIKE '415%' and Phone IS NOT NULL
The output for statements involving the LIKE keyword depends on the sort order chosen during installation. For more information about the effects of different sort orders, see Working with Collations.
The only WHERE conditions that you can use on text columns are LIKE, IS NULL, or PATINDEX.
Wildcards used without LIKE are interpreted as constants instead of as a pattern, that is they represent only their own values. The following query tries to find any phone numbers that consist of the four characters
415% only. It will not find phone numbers that start with
415. For more information about constants, see Constants (Database Engine).
SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone = '415%'
Another important consideration in using wildcards is their effect on performance. If a wildcard begins the expression, an index cannot be used. (Just as you would not know where to start in a phone book if given the name '%mith', not 'Smith'.) A wildcard in or at the end of an expression does not preclude use of an index. (Just as in a phone book, you would know where to search if the name was 'Samuel%', regardless of whether the names Samuels and Samuelson are both there.)
Searching for Wildcard Characters
You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:
Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string
5%anywhere in a string, use:
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
LIKEclause, the leading and ending percent signs (
%) are interpreted as wildcards, and the percent sign preceded by a slash (
/) is interpreted as the % character.
Use square brackets (
[ ]) to enclose the wildcard by itself. To search for a hyphen (
-), instead of using it to specify a search range, use the hyphen as the first character inside a set of brackets:
WHERE ColumnA LIKE '9[-]5'
The following table shows the use of wildcards enclosed in square brackets.
5 followed by any string of 0 or more characters
an, in, on (and so on)
a, b, c, d, or f
-, a, c, d, or f
LIKE '[ [ ]'
When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and trailing blank (space). If a comparison to return all rows with a string LIKE 'abc ' (abc followed by a single space) is requested, a row in which the value of that column is abc (abc without a space) is not returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE 'abc' (abc without a space) is requested, all rows that start with abc and have zero or more trailing blanks are returned.
% (Wildcard - Character(s) to Match) (Transact-SQL)
[ ] (Wildcard - Character(s) to Match) (Transact-SQL)
[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)
_ (Wildcard - Match One Character) (Transact-SQL)