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.
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
Is any valid SQL Server expression of character string data type.
Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.
|%||Any string of zero or more characters.||WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.|
|_ (underscore)||Any single character.||WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).|
|[ ]||Any single character within the specified range ([a-f]) or set ([abcdef]).||WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.|
|[^]||Any single character not within the specified range ([^a-f]) or set ([^abcdef]).||WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.|
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.
LIKE returns TRUE if the match_expression matches the specified pattern.
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.
|LIKE '[a-cdf]'||a, b, c, d, or f|
|LIKE '[-acdf]'||-, a, c, d, or f|
|LIKE '[ [ ]'||[|
|LIKE 'abc[_]d%'||abc_d and abc_de|
|LIKE 'abc[def]'||abcd, abce, and abcf|
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
If there is no character after an escape character in the LIKE pattern, the pattern is not valid and the LIKE returns FALSE. If the character after an escape character is not a wildcard, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the wildcard characters '%', '_', and '[' when they are enclosed in the '' characters. Also, within the '' characters, escape characters can be used and the characters '^', '-' and ']' can be escaped.
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