Wildcards and the LIKE Clause

The LIKE keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime, and smalldatetime data. It takes four wildcard characters, which are in the form of regular expressions:



Wildcard	Meaning

%	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])

When you use LIKE, be sure to enclose the wildcard(s) and the character string in single quotation marks. For example, using LIKE with the data in the authors table:

LIKE 'Mc%' searches for all names that begin with the letters "Mc" (McBadden).
LIKE '%inger' searches for all names that end with the letters "inger" (Ringer, Stringer).
LIKE '%en%' searches for all names that have the letters "en" (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 to Z (Ringer).
LIKE 'M[^c]%' searches for all names beginning with the letter M that don't have the letter c as the second letter (MacFeather).

This query finds all phone numbers in the authors table that have area code 415:

SELECT phone
FROM authors
WHERE phone LIKE '415%'

You can use NOT LIKE with the same wildcards. To find all phone numbers in the authors table that have area codes other than 415, use either of these equivalent queries:

SELECT phone
FROM authors
WHERE phone NOT LIKE '415%'

SELECT phone
FROM authors
WHERE not phone LIKE '415%'

Important The output for statements involving the LIKE keyword depends on the sort order chosen during installation. For information about the effects of different sort orders, see Microsoft SQL Server Setup.

The only WHERE conditions that you can use on text columns are LIKE, IS NULL, or patindex.

Wildcards used without LIKE are interpreted as literals rather than as a pattern ¾ that is, they represent only their own values. The following query attempts to find any phone numbers that consist of the four characters 415% only. It will not find phone numbers that start with 415.

SELECT phone
FROM authors
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 wouldn't know where to start in a phone book if given the name '%mith', not 'Smith'. You'd have to search the whole book!) A wildcard in or at the end of an expression does not preclude use of an index (just as in a phone book, you'd know where to search if the name was 'Samuel%', regardless of whether the names Samuels and Samuelson are both there).

You can search for the wildcard characters themselves. To use wildcards as characters in a LIKE match string rather than as wildcards, use square brackets ([ ]) to enclose %, _, and [. Use ] by itself. To search for a dash (-), rather than using it to specify a search range, use the dash as the first character inside a set of brackets.



Symbol	Meaning

LIKE '5%'	5 followed by any string of 0 or more characters
LIKE '5[%]'	5%
LIKE '_n'	an, in, on (and so on)
LIKE '[_]n'	_n
LIKE '[a-cdf]'	a, b, c, d, or f
LIKE '[-acdf]'	-, a, c, d, or f
LIKE '[ [ ]'	[
LIKE ']'	]

Trailing blanks following the % wildcard in a LIKE clause are truncated to a single trailing blank. LIKE '% ', which includes two trailing blanks, matches any string containing one or more trailing blanks.

Sometimes you might want trailing blanks and trailing underscores (_) to be ignored with the LIKE pattern. To enable this functionality with LIKE, turn on trace flag 204 (at server startup, use the -T204 option), or individual users can enable this functionality by using DBCC TRACEON (204).