Applies to:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL analytics endpoint in Microsoft FabricWarehouse in Microsoft FabricSQL database in Microsoft Fabric Preview

Determines whether a specific 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. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes theLIKE operator more flexible than using the= and!= string comparison operators. If any one of the arguments isn't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible.

Transact-SQL syntax conventions

Syntax

Syntax for SQL Server and Azure SQL Database:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Syntax for Azure Synapse Analytics and Parallel Data Warehouse:

match_expression [ NOT ] LIKE pattern

ESCAPE andSTRING_ESCAPE are not supported in Azure Synapse Analytics or Analytics Platform System (PDW).

Arguments

match_expression

Any validexpression of character data type.

pattern

The specific string of characters to search for inmatch_expression, and can include valid wildcard characters in the following table.pattern can be a maximum of 8,000 bytes.

Ifmatch_expression is a higher precedence data type thanpattern, and thepattern length is greater thanmatch_expression, you will get a truncation error during the implicit conversion ofpattern value tomatch_expression type.

escape_character

A character put in front of a wildcard character to indicate that the wildcard is interpreted as a regular character and not as a wildcard.escape_character is a character expression that has no default and must evaluate to only one character.

Result type

Boolean

Result value

LIKE returns TRUE if thematch_expression matches the specifiedpattern.

Remarks

When you do string comparisons by usingLIKE, all characters in the pattern string are significant. Significant characters include any leading or trailing spaces. If a comparison in a query is to return all rows with a stringLIKE 'abc ' (abc followed by a single space), a row in which the value of that column isabc (abc without a space) isn't 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 stringLIKE 'abc' (abc without a space), all rows that start withabc and have zero or more trailing blanks are returned.

A string comparison using a pattern that containschar andvarchar data may not pass aLIKE comparison because of how the data is stored for each data type. The following example passes a localchar variable to a stored procedure, and then uses pattern matching to find all employees whose last names start with the specified set of characters.

-- Uses AdventureWorksCREATE PROCEDURE FindEmployee @EmpLName CHAR(20)ASSELECT @EmpLName = RTRIM(@EmpLName) + '%';SELECT p.FirstName,    p.LastName,    a.CityFROM Person.Person pINNER JOIN Person.Address a    ON p.BusinessEntityID = a.AddressIDWHERE p.LastName LIKE @EmpLName;GOEXEC FindEmployee @EmpLName = 'Barb';GO

In theFindEmployee procedure, no rows are returned because thechar variable (@EmpLName) contains trailing blanks whenever the name contains fewer than 20 characters. Because theLastName column isvarchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

However, the following example succeeds because trailing blanks aren't added to avarchar variable.

-- Uses AdventureWorks  CREATE PROCEDURE FindEmployee @EmpLName VARCHAR(20)ASSELECT @EmpLName = RTRIM(@EmpLName) + '%';SELECT p.FirstName,    p.LastName,    a.CityFROM Person.Person pINNER JOIN Person.Address a    ON p.BusinessEntityID = a.AddressIDWHERE p.LastName LIKE @EmpLName;GOEXEC FindEmployee @EmpLName = 'Barb';GO

Here's the result set.

FirstName      LastName            City----------     -------------------- ---------------Angela         Barbariol            SnohomishDavid          Barber               Snohomish(2 row(s) affected)

Pattern match using LIKE

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression,pattern, andescape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one 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 ornvarchar data types) withLIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks aren't significant. UnicodeLIKE is compatible with the ISO standard. ASCIILIKE is compatible with earlier versions of SQL Server.

The following series of examples shows the differences in rows returned between ASCII and UnicodeLIKE pattern matching.

-- ASCII pattern matching with char columnCREATE TABLE t (col1 CHAR(30));INSERT INTO tVALUES ('Robert King');SELECT * FROM tWHERE col1 LIKE '% King'; -- returns 1 row-- Unicode pattern matching with nchar columnCREATE TABLE t (col1 NCHAR(30));INSERT INTO tVALUES ('Robert King');SELECT * FROM tWHERE col1 LIKE '% King'; -- no rows returned-- Unicode pattern matching with nchar column and RTRIMCREATE TABLE t (col1 NCHAR(30));INSERT INTO tVALUES ('Robert King');SELECT * FROM tWHERE RTRIM(col1) LIKE '% King'; -- returns 1 row

Use the% wildcard character

If theLIKE '5%' symbol is specified, the Database Engine searches for the number5 followed by any string of zero or more characters.

For example, the following query shows all dynamic management views in theAdventureWorks2022 database, because they all start with the lettersdm.

-- Uses AdventureWorks  SELECT NameFROM sys.system_viewsWHERE Name LIKE 'dm%';GO

To see all objects that aren't dynamic management views, useNOT LIKE 'dm%'. If you have a total of 32 objects andLIKE finds 13 names that match the pattern,NOT LIKE finds the 19 objects that don't match theLIKE pattern.

You may not always find the same names with a pattern such asLIKE '[^d][^m]%'. Instead of 19 names, you may find only 14, with all the names that start withd or havem as the second letter eliminated from the results, and the dynamic management view names. This behavior is because match strings with negative wildcard characters are evaluated in steps, one wildcard at a time. If the match fails at any point in the evaluation, it's eliminated.

Use 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 following table shows several examples of using theLIKE keyword and the[ ] wildcard characters.

Pattern match 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 a 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 that contain the string 30% anywhere in the comment column, specify a WHERE clause such asWHERE comment LIKE '%30!%%' ESCAPE '!'. If ESCAPE and the escape character aren't specified, the Database Engine returns any rows with the string30!.

If there is no character after an escape character in the LIKE pattern, the pattern isn't valid and the LIKE returns FALSE. If the character after an escape character isn't a wildcard character, the escape character is discarded and the following character is treated as a regular character in the pattern. These characters include the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Escape characters can be used within the double bracket characters ([ ]), including to escape a caret (^), hyphen (-), or right bracket (]).

0x0000 (char(0)) is an undefined character in Windows collations, and can't be included in LIKE.

Examples

A. Use LIKE with the% wildcard character

The following example finds all telephone numbers that have area code415 in thePersonPhone table.

-- Uses AdventureWorks  SELECT p.FirstName,    p.LastName,    ph.PhoneNumberFROM Person.PersonPhone AS phINNER JOIN Person.Person AS p    ON ph.BusinessEntityID = p.BusinessEntityIDWHERE ph.PhoneNumber LIKE '415%'ORDER BY p.LastName;GO

Here's the result set.

FirstName             LastName             Phone-----------------     -------------------  ------------Ruben                 Alonso               415-555-124Shelby                Cook                 415-555-0121Karen                 Hu                   415-555-0114John                  Long                 415-555-0147David                 Long                 415-555-0123Gilbert               Ma                   415-555-0138Meredith              Moreno               415-555-0131Alexandra             Nelson               415-555-0174Taylor                Patterson            415-555-0170Gabrielle              Russell             415-555-0197Dalton                 Simmons             415-555-0115(11 row(s) affected)

B. Use NOT LIKE with the% wildcard character

The following example finds all telephone numbers in thePersonPhone table that have area codes other than415.

-- Uses AdventureWorksSELECT p.FirstName,    p.LastName,    ph.PhoneNumberFROM Person.PersonPhone AS phINNER JOIN Person.Person AS p    ON ph.BusinessEntityID = p.BusinessEntityIDWHERE ph.PhoneNumber NOT LIKE '415%'    AND p.FirstName = 'Gail'ORDER BY p.LastName;GO

Here's the result set.

FirstName              LastName            Phone---------------------- -------------------- -------------------Gail                  Alexander            1 (11) 500 555-0120Gail                  Butler               1 (11) 500 555-0191Gail                  Erickson             834-555-0132Gail                  Erickson             849-555-0139Gail                  Griffin              450-555-0171Gail                  Moore                155-555-0169Gail                  Russell              334-555-0170Gail                  Westover             305-555-0100(8 row(s) affected)

C. Use the ESCAPE clause

The following example uses theESCAPE clause and the escape character to find the exact character string10-15% in columnc1 of themytbl2 table.

USE tempdb;GOIF EXISTS (        SELECT TABLE_NAME        FROM INFORMATION_SCHEMA.TABLES        WHERE TABLE_NAME = 'mytbl2'        )    DROP TABLE mytbl2;GOUSE tempdb;GOCREATE TABLE mytbl2 (c1 SYSNAME);GOINSERT mytbl2VALUES ('Discount is 10-15% off'),    ('Discount is .10-.15 off');GOSELECT c1FROM mytbl2WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';GO

D. Use the[ ] wildcard characters

The following example finds employees on thePerson table with the first name ofCheryl orSheryl.

-- Uses AdventureWorksSELECT BusinessEntityID,    FirstName,    LastNameFROM Person.PersonWHERE FirstName LIKE '[CS]heryl';GO

The following example finds the rows for employees in thePerson table with last names ofZheng orZhang.

-- Uses AdventureWorks  SELECT LastName,    FirstNameFROM Person.PersonWHERE LastName LIKE 'Zh[ae]ng'ORDER BY LastName ASC,    FirstName ASC;GO

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Use LIKE with the% wildcard character

The following example finds all employees in theDimEmployee table with telephone numbers that start with612.

-- Uses AdventureWorks  SELECT FirstName,    LastName,    PhoneFROM DimEmployeeWHERE phone LIKE '612%'ORDER BY LastName;

F. Use NOT LIKE with the% wildcard character

The following example finds all telephone numbers in theDimEmployee table that don't start with612.

-- Uses AdventureWorks  SELECT FirstName,    LastName,    PhoneFROM DimEmployeeWHERE phone NOT LIKE '612%'ORDER BY LastName;

G. Use LIKE with the_ wildcard character

The following example finds all telephone numbers that have an area code starting with6 and ending in2 in theDimEmployee table. The % wildcard character is included at the end of the search pattern to match all following characters in the phone column value.

-- Uses AdventureWorks  SELECT FirstName,    LastName,    PhoneFROM DimEmployeeWHERE phone LIKE '6_2%'ORDER BY LastName;

See also

• PATINDEX (Transact-SQL)
• Expressions (Transact-SQL)
• What are the SQL database functions?
• SELECT (Transact-SQL)
• SELECT (Transact-SQL)
• WHERE (Transact-SQL)