LIKE

Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of character string data type, Microsoft® SQL Server™ converts them to character string data type, if possible.

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Arguments

match_expression

Is any valid SQL Server expression of character string data type.

pattern

Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.

escape_character

Is any valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character.

Result Types

Boolean

Result Value

LIKE returns TRUE if the match_expression matches the specified pattern.

Remarks

When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

A string comparison using a pattern containing 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 data type and where a LIKE comparison may fail. The following example passes a local char variable to a stored procedure and then uses pattern matching 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 are returned because the char variable (@AU_LNAME) contains trailing blanks whenever the name contains fewer than 20 characters. Because the au_lname column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

However, this example succeeds because trailing blanks are not added to a varchar variable:

USE pubs
GO
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 + '%'

EXEC find_books2 'ring'

Here is the result set:

title_id title                                                                            
-------- ---------------------------------------------------------------
MC3021   The Gourmet Microwave                                                            
PS2091   Is Anger the Enemy?                                                              
PS2091   Is Anger the Enemy?                                                              
PS2106   Life Without Fear                                                                

(4 row(s) affected)

Pattern Matching with LIKE

It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.

Here is a series of examples that show the differences in rows returned between ASCII and Unicode LIKE pattern matching:

-- ASCII pattern matching with char column
CREATE TABLE t (col1 char(30))
INSERT INTO t VALUES ('Robert King')
SELECT * 
FROM t 
WHERE col1 LIKE '% King'   -- returns 1 row

-- Unicode pattern matching with nchar column
CREATE TABLE t (col1 nchar(30))
INSERT INTO t VALUES ('Robert King')
SELECT * 
FROM t 
WHERE col1 LIKE '% King'   -- no rows returned

-- Unicode pattern matching with nchar column and RTRIM
CREATE TABLE t (col1 nchar (30))
INSERT INTO t VALUES ('Robert King')
SELECT * 
FROM t 
WHERE RTRIM(col1) LIKE '% King'   -- returns 1 row

Note  When you perform string comparisons with LIKE, all characters in the pattern string are significant, including every leading or trailing blank (space).

Using the % Wildcard Character

If the LIKE '5%' symbol is specified, SQL Server searches for the number 5 followed by any string of zero or more characters.

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

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sys%'

Note  Be aware that system tables can change from version to version. It is recommended that you use the Information Schema Views or applicable stored procedures to work with SQL Server system tables.

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 may not always find the same names with a pattern such as LIKE '[^s][^y][^s]%'. Instead of 19 names, you may 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 is 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.

Using Wildcard Characters as Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Pattern Matching with the ESCAPE Clause

You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in the customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows containing the string 30% anywhere in the comment column, specify a WHERE clause of WHERE comment LIKE '%30!%%' ESCAPE '!'. Unless ESCAPE and the escape character are specified, SQL Server returns any rows with the string 30.

This example shows how to search for the string "50% off when 100 or more copies are purchased" in the notes column of the titles table in the pubs database:

USE pubs
GO
SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased' 
   ESCAPE '%'
GO

Examples

A. Use LIKE with the % wildcard character

This example finds all phone numbers that have area code 415 in the authors table.

USE pubs
GO
SELECT phone
FROM authors
WHERE phone LIKE '415%'
ORDER by au_lname
GO

Here is the result set:

phone        
------------ 
415 658-9932 
415 548-7723 
415 836-7128 
415 986-7020 
415 836-7128 
415 534-9219 
415 585-4620 
415 354-7128 
415 834-2919 
415 843-2991 
415 935-4228 

(11 row(s) affected)

B. Use NOT LIKE with the % wildcard character

This example finds all phone numbers in the authors table that have area codes other than 415.

USE pubs
GO
SELECT phone
FROM authors
WHERE phone NOT LIKE '415%'
ORDER BY au_lname
GO

Here is the result set:

phone        
------------ 
503 745-6402 
219 547-9982 
615 996-8275 
615 297-2723 
707 938-6445 
707 448-4982 
408 286-2428 
301 946-8853 
801 826-0752 
801 826-0752 
913 843-0462 
408 496-7223 

(12 row(s) affected)

C. Use the ESCAPE clause

This example uses the ESCAPE clause and the escape character to find the exact character string 10-15% in column c1 of the mytbl2 table.

USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'mytbl2')
   DROP TABLE mytbl2
GO
USE pubs
GO
CREATE TABLE mytbl2
(
 c1 sysname
)
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off')
INSERT mytbl2 VALUES ('Discount is .10-.15 off')
GO
SELECT c1 
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'
GO

D. Use the [ ] wildcard characters

This example finds authors with the first name of Cheryl or Sheryl.

USE pubs
GO
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_fname LIKE '[CS]heryl'
ORDER BY au_lname ASC, au_fname ASC
GO

This example finds the rows for authors with last names of Carson, Carsen, Karson, or Karsen.

USE pubs
GO
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE '[CK]ars[eo]n'
ORDER BY au_lname ASC, au_fname ASC
GO

See Also

Expressions

Functions

SELECT

WHERE