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. The LIKE keyword uses a regular expression to contain the pattern the values are matched against. The pattern contains the character string to search for, which can contain any combination of four wildcards.

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

Enclose the wildcard(s) and the character string in single quotation marks, for example:

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

SELECT phone

FROM pubs.dbo.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 pubs.dbo.authors

WHERE phone NOT LIKE '415%'

  

-- Or

  

SELECT phone

FROM pubs.dbo.authors

WHERE NOT phone LIKE '415%'

  

The IS NOT NULL clause can be used with wildcards and the LIKE clause. For example, this query retrieves telephone numbers from the authors table in which the telephone number begins with 415 and IS NOT NULL:

USE pubs

SELECT phone

FROM authors

WHERE phone LIKE '415%' and phone IS NOT NULL

  


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 Sort Order.


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 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. For information about constants, see Using Constants.

SELECT phone

FROM pubs.dbo.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’.) 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).

Searching for Wildcard Characters

You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:

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

When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and/or 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.

See Also
LIKE WHERE

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.