Wildcard Characters

Wildcard characters are used with the LIKE keyword to represent any character in a string when searching for a char, varchar, or datetime value.

Syntax

expression [NOT] LIKE 'string'

The string can include these wildcard characters:

Remarks

You can use the LIKE keyword and wildcard characters with datetime data as well as with char and varchar data. When you use LIKE with datetime values, SQL Server converts the dates to the standard datetime format and then to varchar. Since the standard storage format doesn't include seconds or milliseconds, you cannot search for seconds or milliseconds with LIKE and a pattern. It is a good idea to use LIKE when you search for datetime values, since datetime entries can contain a variety of date parts. For example, if you insert the value 9:20 into a column named arrival_time, the clause WHERE arrival_time = '9:20' does not find the value because SQL Server converts the entry into 'Jan 1, 1900 9:20 AM'. However, the clause WHERE arrival_time LIKE '%9:20%' does find the value.

To use %, _, [ ], or [^] as literal characters in a LIKE string rather than as wildcards, use square brackets around the percent sign, the underscore, and the open bracket. Use the close bracket by itself. Use the dash as the first character inside a set of square brackets, as shown here:

You can't always duplicate NOT LIKE patterns with LIKE and the negative wildcard [^] because NOT LIKE finds the items that do not match the entire LIKE pattern; LIKE with negative wildcards is evaluated one character at a time.

For example, this query shows all system tables in a database, because they all begin with the letters sys:

SELECT name
FROM sysobjects
WHERE name LIKE 'sys%'

To see all objects that are not system tables, use NOT LIKE 'sys%'. If you have a total of 32 objects and LIKE finds 13 names that match the pattern, NOT LIKE finds the 19 objects that do not match the LIKE pattern.

You won't always find the same names with a pattern such as LIKE '[^s][^y][^s]%'. Instead of 19 names, you might get only 14, with all the names that begin with "s" or have "y" as the second letter or have "s" as the third letter eliminated from the results as well as the system table names. This result occurs because match strings with negative wildcards are evaluated in steps, one wildcard at a time. If the match fails at any point in the evaluation, it is eliminated.

Pattern Matching with Blanks

When performing string comparisons 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 where the value of that column is 'abc' (abc without a space) will not be 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 would be returned.

A string comparison using a pattern char and varchar data may not pass a LIKE comparison because of how the data is stored. It is important to understand the storage for each datatype and where a LIKE comparison may fail. In the following example, a local char variable is passed to a stored procedure and then pattern matching is used to find all of the books by a certain author. In this procedure, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @au_lname char(20)
AS
SELECT @au_lname = RTRIM(@au_lname)  '%'
SELECT t.title_id, t.title 
    FROM authors a, titleauthor ta, titles t
        WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
        AND a.au_lname LIKE @au_lname

In the find_books procedure, no rows will be returned because the char variable (@au_lname) will contain trailing blanks whenever the name contains fewer than 20 characters. Because the au_lname column is varchar, there are no trailing blanks. This procedure will fail because the trailing blanks are significant.

This example succeeds because trailing blanks are not added to a varchar variable:

CREATE PROCEDURE find_books2 @au_lname varchar(20)
AS
SELECT t.title_id, t.title 
    FROM authors a, titleauthor ta, titles t
        WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
        AND a.au_lname LIKE @au_lname  '%'

Examples

A.    Pattern Matching with LIKE and %

This example finds all phone numbers in the authors table that begin with the 415 area code:

SELECT phone
    FROM authors
        WHERE phone LIKE '415%'

B.    Pattern Matching and Placement of NOT

These two queries are equivalent: they find all phone numbers in the authors table that do not begin with the 415 area code:

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

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

C.    Multiple Wildcards

This example finds all names that have the characters "en" in them (Bennet, Green, McBadden):

SELECT au_lname
    FROM authors
        WHERE au_lname LIKE '%en%'

D.    Single Character Wildcard

This example finds six-letter names that end with "heryl" (Cheryl or Sheryl):

SELECT au_fname
    FROM authors
        WHERE au_fname LIKE '_heryl'

E.    Single Character Range

This example finds names ending with "inger" and beginning with any single character between "M" and "Z":

SELECT au_lname
    FROM authors
        WHERE au_lname LIKE '[M-Z]inger'

F.    Single Character Listing

This example finds both DeFrance and deFrance:

SELECT au_lname
    FROM authors
        WHERE au_lname LIKE '[dD]eFrance'

G.    NOT a Single Character

This example finds names beginning with "M" that have a second letter that is not "c":

SELECT au_lname
    FROM authors
        WHERE au_lname LIKE 'M[^c]%'

See Also