Home

	Expand Search

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Pattern matching

Matching character strings: like

Matching character strings: like

The like keyword searches for a character string that matches a pattern. like is used with char, varchar, nchar, nvarchar, unichar, univarchar binary, varbinary, text, and datetime data.

The syntax for like is:

{where | having} [not] 
     column_name [not] like  "match_string" 

match_string can include the symbols in Table 2-4:

You can match the column data to constants, variables, or other columns that contain the wildcard characters shown in Table 2-4. When using constants, enclose the match strings and character strings in quotation marks. For example, using like with the data in the authors table:

• like "Mc%" searches for every name that begins with ''Mc'' (McBadden).

• like "%inger" searches for every name that ends with ''inger'' (Ringer, Stringer).

• like "%en%" searches for every name containing ''en'' (Bennet, Green, McBadden).

• like "_heryl" searches for every six-letter name ending with ''heryl'' (Cheryl).

• like "[CK]ars[eo]n" searches for ''Carsen,'' ''Karsen,'' ''Carson,'' and ''Karson'' (Carson).

• like "[M-Z]inger" searches for all names ending with ''inger'' that begin with any single letter from M to Z (Ringer).

• like "M[^c]%" searches for all names beginning with ''M'' that do not have ''c'' as the second letter.

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

select phone 
from authors 
where phone like "415%"

The only where condition you can use on text columns is like. This query finds all the rows in the blurbs table where the copy column includes the word "computer":

select * from blurbs 
where copy like "%computer%"

Adaptive Server interprets wildcard characters used without like as literals rather than as a pattern; they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters "415%" only. It does not find phone numbers that start with 415.

select phone 
from authors 
where phone = "415%"

When you use like with datetime values, Adaptive Server converts the dates to the standard datetime format, and then to varchar or univarchar. Since the standard storage format does not 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 may contain a variety of date parts. For example, if you insert the value "9:20" and the current date into a column named arrival_time, this query will not find the value, because Adaptive Server converts the entry into "Jan 1 1900 9:20AM":

where arrival_time = "9:20"

However, the clause below finds the 9:20 value:

where arrival_time like "%9:20%"

You can use the same wildcard characters with not like, that you can use with like. For example, to find all the phone numbers in the authors table that do not have 415 as the area code, you can use either of these queries:

select phone 
from authors 
where phone not like "415%"

select phone 
from authors 
where not phone like "415%"

You cannot always duplicate not like patterns with like and the negative wildcard character [^]. Match strings with negative wildcard characters are evaluated in steps, one character at a time. If the match fails at any point in the evaluation, it is eliminated.

For example, this query finds the system tables in a database whose names begin with "sys":

select name 
from sysobjects 
where name like "sys%" 

If you have a total of 32 objects and like finds 13 names that match the pattern, not like will find the 19 objects that do not match the pattern.

where name not like "sys%"

A pattern such as the following may not produce the same results:

like [^s][^y][^s]%

Instead of 19, 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.

You can search for wildcard characters by escaping them and searching for them as literals. There are two ways to use the wildcard characters as literals in a like match string: square brackets and the escape clause. The match string can also be a variable or a value in a table that contains a wildcard character.

Square brackets (Transact-SQL extension)

Use square brackets for the percent sign, the underscore, and right and left brackets. To search for a dash, rather than using it to specify a range, use the dash as the first character inside a set of brackets.

escape clause (SQL-compliant)

Use the escape clause to specify an escape character in the like clause. An escape character must be a single character string. Any character in the server's default character set can be used.

An escape character is valid only within its like clause and has no effect on other like clauses in the same statement.

The only characters that are valid following an escape character are the wildcard characters ( _ , % , [ , ] , and [^] ), and the escape character itself. The escape character affects only the character following it. If a pattern contains two literal occurrences of a character that happens to be an escape character, the string must contain four consecutive escape characters (see the last example in Table 2-6). Otherwise, Adaptive Server raises a SQLSTATE error condition and returns an error message.

Specifying more than one escape character raises a SQLSTATE error condition, and Adaptive Server returns an error message:

like "%XX_%" escape "XX"
like "%XX%X_%" escape "XX"

An escape character retains its special meaning within square brackets, unlike the wildcard characters. Do not use existing wildcard characters as escape characters in the escape clause, for these reasons:

• If you specify "_" or "%" as an escape character, it loses its special meaning within that like clause and acts only as an escape character.

• If you specify "["or "]" as an escape character, the Transact-SQL meaning of the bracket is disabled within that like clause.

• If you specify "-" or "^" as an escape character, it loses the special meaning that it normally has within square brackets and acts only as an escape character.

Adaptive Server truncates trailing blanks following "%" in a like clause to a single trailing blank. like ''% '' (percent sign followed by 2 spaces) matches ''X '' (one space); ''X '' (two spaces); ''X '' (three spaces), or any number of trailing spaces.

You can use wildcard characters for columns and column names. You might want to create a table called special_discounts in the pubs2 database to run a price projection for a special sale:

create table special_discounts
id_type char(3), discount int)
insert into special_discounts 
values("BU%", 10)

...

The table should contain the following data:

id_type discount     
------- -----------  
BU%              10  
PS%              12  
MC%              15 

The following query uses wildcard characters in id_type in the where clause:

select title_id, discount, price, price - (price*discount/100) 
from special_discounts, titles 
where title_id like id_type 

Here are the results of that query:

 title_id   discount      price                                              
 -------- ----------- -------------- --------------  
 BU1032            10          19.99          17.99  
 BU1111            10          11.95          10.76  
 BU2075            10           2.99           2.69  
 BU7832            10          19.99          17.99  
 PS1372            12          21.59          19.00  
 PS2091            12          10.95           9.64  
 PS2106            12           7.00           6.16  
 PS3333            12          19.99          17.59  
 PS7777            12           7.99           7.03  
 MC2222            15          19.99          16.99  
 MC3021            15           2.99           2.54  
 MC3026            15           NULL           NULL  
 
(12 rows affected) 

This permits sophisticated pattern matching without having to construct a series of or clauses.