LIKE (Transact-SQL)LIKE (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

确定特定字符串是否与指定模式相匹配。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. 与使用 = 和 != 字符串比较运算符相比,使用通配符可使 LIKE 运算符更加灵活。Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. 如果任一参数都不属于字符串数据类型,SQL Server 数据库引擎SQL Server Database Engine会尽量将它转换为使用字符串数据类型。If any one of the arguments isn't of character string data type, the SQL Server 数据库引擎SQL Server Database Engine converts it to character string data type, if it's possible.

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
match_expression [ NOT ] LIKE pattern  

参数Arguments

match_expression match_expression
为任何有效的字符数据类型的表达式Is any valid expression of character data type.

pattern pattern
要在 match_expression 中搜索并且可以包括下列有效通配符的特定字符串 。Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern 的最大长度可达 8,000 字节 。pattern can be a maximum of 8,000 bytes.

通配符Wildcard character 描述Description 示例Example
% 包含零个或多个字符的任意字符串。Any string of zero or more characters. WHERE title LIKE '%computer%' 将查找在书名中任意位置包含单词 "computer" 的所有书名。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' 将查找以 ean 结尾的所有 4 个字母的名字(Dean、Sean 等)。WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ][ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' 将查找以 arsen 结尾并且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如 Carsen、Larsen、Karsen 等。WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. 在范围搜索中,范围包含的字符可能因排序规则的排序规则而异。In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^][^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE 'de[^l]%' 查找以 de 开头且后跟字母不是 l 的所有作者姓氏。WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter isn't l.

escape_character escape_character
位于通配符前的字符,用于指明应将通配符解释为常规字符,而不是通配符。Is 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 是字符表达式,无默认值,并且计算结果必须仅为一个字符 。escape_character is a character expression that has no default and must evaluate to only one character.

结果类型Result Types

BooleanBoolean

结果值Result Value

如果 match_expression 与指定的 pattern 相匹配,则 LIKE 返回 TRUE 。LIKE returns TRUE if the match_expression matches the specified pattern.

RemarksRemarks

如果你使用 LIKE 执行字符串比较,模式字符串中的所有字符都有意义。When you do string comparisons by using LIKE, all characters in the pattern string are significant. 有意义的字符包括任何前导或尾随空格。Significant characters include any leading or trailing spaces. 如果查询中的比较要返回包含字符串 LIKE 'abc '(abc 后跟一个空格)的所有行,则不会返回列值为 abc(abc 后没有空格)的行。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) isn't returned. 但是可以忽略模式所要匹配的表达式中的尾随空格。However, trailing blanks, in the expression to which the pattern is matched, are ignored. 如果查询中的比较要返回包含 "abc"(abc 后没有空格)的所有行,则返回以 "abc" 开始并且具有零个或多个尾随空格的所有行。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.

鉴于各数据类型的数据存储方式不同,如果使用包含 char 和 varchar 数据的模式进行字符串比较,可能无法传递 LIKE 比较。A string comparison using a pattern that contains char and varchar data may not pass a LIKE comparison because of how the data is stored for each data type. 下面的示例先将本地 char 变量传递给存储过程,再使用模式匹配来查找姓氏以一组指定字符开头的所有员工。The following example passes a local char 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 AdventureWorks  
  
CREATE PROCEDURE FindEmployee @EmpLName char(20)  
AS  
SELECT @EmpLName = RTRIM(@EmpLName) + '%';  
SELECT p.FirstName, p.LastName, a.City  
FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID  
WHERE p.LastName LIKE @EmpLName;  
GO  
EXEC FindEmployee @EmpLName = 'Barb';  
GO  

当名字中包含的字符数小于 20 时,char 变量 (@EmpLName) 将包含尾随空格,这导致 FindEmployee 过程中没有行返回 。In the FindEmployee procedure, no rows are returned because the char variable (@EmpLName) contains trailing blanks whenever the name contains fewer than 20 characters. 由于 LastName 列为 varchar 类型,因此没有尾随空格 。Because the LastName column is varchar, there are no trailing blanks. 因为尾随空格是有意义的,所以此过程失败。This procedure fails because the trailing blanks are significant.

但以下示例会成功,因为没有向 varchar 变量中添加尾随空格。However, the following example succeeds because trailing blanks aren't added to a varchar variable.

-- Uses AdventureWorks  
  
CREATE PROCEDURE FindEmployee @EmpLName varchar(20)  
AS  
SELECT @EmpLName = RTRIM(@EmpLName) + '%';  
SELECT p.FirstName, p.LastName, a.City  
FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID  
WHERE p.LastName LIKE @EmpLName;  
GO  
EXEC FindEmployee @EmpLName = 'Barb';  

下面是结果集:Here is the result set.

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

使用 LIKE 的模式匹配Pattern Matching by Using LIKE

LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配。LIKE supports ASCII pattern matching and Unicode pattern matching. 如果所有参数(match_expression、pattern 和 escape_character,如果存在)均为 ASCII 字符数据类型,则执行 ASCII 模式匹配 。When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. 如果任一参数为 Unicode 数据类型,所有参数都会转换为 Unicode,且执行的是 Unicode 模式匹配。If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. 如果将 Unicode 数据(nchar 或 nvarchar 数据类型)与 LIKE 结合使用,尾随空格很重要;但对于非 Unicode 数据,尾随空格并不重要。When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks aren't significant. Unicode LIKE 与 ISO 标准兼容。Unicode LIKE is compatible with the ISO standard. ASCII LIKE 与旧版 SQL ServerSQL Server 兼容。ASCII LIKE is compatible with earlier versions of SQL ServerSQL Server.

下面的一系列示例展示了 ASCII LIKE 模式匹配与 Unicode LIKE 模式匹配返回的行的差异。The following 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  

备注

LIKE 比较受排序规则影响。LIKE comparisons are affected by collation. 有关详细信息,请参阅排序规则 (Transact-SQL)For more information, see COLLATE (Transact-SQL).

使用 % 通配符Using the % Wildcard Character

如果指定 LIKE '5%' 符号,则数据库引擎Database Engine将搜索后跟零个或多个任意字符的数字 5。If the LIKE '5%' symbol is specified, the 数据库引擎Database Engine searches for the number 5 followed by any string of zero or more characters.

例如,以下查询显示 AdventureWorks2012AdventureWorks2012 数据库中的所有动态管理视图,因为它们全部以字母 dm 开始。For example, the following query shows all dynamic management views in the AdventureWorks2012AdventureWorks2012 database, because they all start with the letters dm.

-- Uses AdventureWorks  
  
SELECT Name  
FROM sys.system_views  
WHERE Name LIKE 'dm%';  
GO  

若要查看所有非动态管理视图的对象,请使用 NOT LIKE 'dm%'To see all objects that aren't dynamic management views, use NOT LIKE 'dm%'. 如果共有 32 个对象,且 LIKE 找到 13 个名称与模式匹配,那么 NOT LIKE 就会找到 19 个对象与 LIKE 模式不匹配。If you have a total of 32 objects and LIKE finds 13 names that match the pattern, NOT LIKE finds the 19 objects that don't match the LIKE pattern.

使用 LIKE '[^d][^m]%' 之类的模式不一定每次找到的名称都相同。You may not always find the same names with a pattern such as LIKE '[^d][^m]%'. 可能仅找到 14 名称(而不是 19 个),除了动态管理视图名称外,所有以 d 开始或第二个字母为 m 的名称也都将从结果中消除。Instead of 19 names, you may find only 14, with all the names that start with d or have m 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.

将通配符作为文字使用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. 下表显示了几个使用 LIKE 关键字和 [ ] 通配符的示例。The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

符号Symbol 含义Meaning
LIKE '5[%]'LIKE '5[%]' 5%5%
LIKE '[]n'LIKE '[]n' _n_n
LIKE '[a-cdf]'LIKE '[a-cdf]' a、b、c、d 或 fa, b, c, d, or f
LIKE '[-acdf]'LIKE '[-acdf]' -、a、c、d 或 f-, a, c, d, or f
LIKE '[ [ ]'LIKE '[ [ ]' [[
LIKE ']'LIKE ']' ]]
LIKE 'abc[]d%'LIKE 'abc[]d%' abc_d 和 abc_deabc_d and abc_de
LIKE 'abc[def]'LIKE 'abc[def]' abcd、abce 和 abcfabcd, abce, and abcf

使用 ESCAPE 子句的模式匹配Pattern Matching with the ESCAPE Clause

可搜索包含一个或多个特殊通配符的字符串。You can search for character strings that include one or more of the special wildcard characters. 例如,customers 数据库中的 discounts 表可能存储含百分号 (%) 的折扣值。For example, the discounts table in a customers database may store discount values that include a percent sign (%). 若要搜索作为字符而不是通配符的百分号,必须提供 ESCAPE 关键字和转义符。To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. 例如,一个样本数据库包含名为 comment 的列,该列含文本 30%。For example, a sample database contains a column named comment that contains the text 30%. 若要搜索在 comment 列中的任何位置包含字符串 30% 的任何行,请指定 WHERE comment LIKE '%30!%%' ESCAPE '!' 之类的 WHERE 子句。To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as WHERE comment LIKE '%30!%%' ESCAPE '!'. 如果未指定 ESCAPE 和转义字符,数据库引擎Database Engine 返回包含字符串 30! 的所有行。If ESCAPE and the escape character aren't specified, the 数据库引擎Database Engine returns any rows with the string 30!.

如果 LIKE 模式中的转义字符后面没有字符,此模式无效,且 LIKE 返回 FALSE。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) ) 是 Windows 排序规则中未定义的字符,无法添加到 LIKE 中。0x0000 (char(0)) is an undefined character in Windows collations and can't be included in LIKE.

示例Examples

A.A. 使用带 % 通配符的 LIKEUsing LIKE with the % wildcard character

以下示例在 415 表中查找区号为 PersonPhone 的所有电话号码。The following example finds all telephone numbers that have area code 415 in the PersonPhone table.

-- Uses AdventureWorks  
  
SELECT p.FirstName, p.LastName, ph.PhoneNumber  
FROM Person.PersonPhone AS ph  
INNER JOIN Person.Person AS p  
ON ph.BusinessEntityID = p.BusinessEntityID  
WHERE ph.PhoneNumber LIKE '415%'  
ORDER by p.LastName;  
GO  

下面是结果集:Here is the result set.

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

### B. Using NOT LIKE with the % wildcard character  
The following example finds all telephone numbers in the `PersonPhone` table that have area codes other than `415`.  
 
```sql  
-- Uses AdventureWorks  
 
SELECT p.FirstName, p.LastName, ph.PhoneNumber  
FROM Person.PersonPhone AS ph  
INNER JOIN Person.Person AS p  
ON ph.BusinessEntityID = p.BusinessEntityID  
WHERE ph.PhoneNumber NOT LIKE '415%' AND p.FirstName = 'Gail'  
ORDER BY p.LastName;  
GO  

下面是结果集:Here is the result set.

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

### C. Using the ESCAPE clause  
The following example uses the `ESCAPE` clause and the escape character to find the exact character string `10-15%` in column `c1` of the `mytbl2` table.  
 
```sql
USE tempdb;  
GO  
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
     WHERE TABLE_NAME = 'mytbl2')  
  DROP TABLE mytbl2;  
GO  
USE tempdb;  
GO  
CREATE TABLE mytbl2  
(  
c1 sysname  
);  
GO  
INSERT mytbl2 VALUES ('Discount is 10-15% off'), ('Discount is .10-.15 off');  
GO  
SELECT c1   
FROM mytbl2  
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';  
GO  

D.D. 使用 [ ] 通配符Using the [ ] wildcard characters

以下示例将查找 Person 表中名字为 CherylSheryl 的员工。The following example finds employees on the Person table with the first name of Cheryl or Sheryl.

-- Uses AdventureWorks  
  
SELECT BusinessEntityID, FirstName, LastName   
FROM Person.Person   
WHERE FirstName LIKE '[CS]heryl';  
GO  

以下示例查找 Person 表中姓氏为 ZhengZhang 的员工所对应的行。The following example finds the rows for employees in the Person table with last names of Zheng or Zhang.

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

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

E.E. 使用带 % 通配符的 LIKEUsing LIKE with the % wildcard character

以下示例在 DimEmployee 表中查找电话号码以 612 开头的所有员工。The following example finds all employees in the DimEmployee table with telephone numbers that start with 612.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, Phone  
FROM DimEmployee  
WHERE phone LIKE '612%'  
ORDER by LastName;  

F.F. 使用带 % 通配符的 NOT LIKEUsing NOT LIKE with the % wildcard character

下面的示例在 DimEmployee 表中查找不以 612 开头的所有电话号码。The following example finds all telephone numbers in the DimEmployee table that don't start with 612. .

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, Phone  
FROM DimEmployee  
WHERE phone NOT LIKE '612%'  
ORDER by LastName;  

G.G. 使用带 _ 通配符的 LIKEUsing LIKE with the _ wildcard character

以下示例在 DimEmployee 表中查找区号以 6 开头、以 2 结尾的所有电话号码。The following example finds all telephone numbers that have an area code starting with 6 and ending in 2 in the DimEmployee 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, Phone  
FROM DimEmployee  
WHERE phone LIKE '6_2%'  
ORDER by LastName;   

另请参阅See Also

PATINDEX (Transact-SQL) PATINDEX (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)