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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse 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 Database EngineSQL Server Database Engine 會將它轉換成字元字串資料類型 (若可能的話)。If any one of the arguments isn't of character string data type, the SQL Server Database EngineSQL 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_expressionmatch_expression
這是字元資料類型的任何有效運算式Is any valid expression of character data type.

patternpattern
這是要在 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 DescriptionDescription 範例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 結尾的四個字母的名字 (如 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_characterescape_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

布林Boolean

結果值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. 如果查詢中的某個比較會傳回具有字串 LIKE '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.

使用模式包含 charvarchar 資料的字串比較,可能會因為每個資料類型的資料儲存方式而無法通過 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  

FindEmployee 程序中,不會傳回任何資料列,因為每當名稱包含的字元少於 20 個時,char 變數 (@EmpLName) 就會包含尾端空白。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_expressionpatternescape_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. 當您搭配 LIKE 使用 Unicode 資料 (ncharnvarchar 資料類型) 時,尾端空白很重要;不過,針對非 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 和 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. 如需詳細資訊,請參閱 COLLATE (Transact-SQL)For more information, see COLLATE (Transact-SQL).

使用 % 萬用字元Using the % Wildcard Character

如果指定了 LIKE '5%' 符號,Database EngineDatabase Engine 會搜尋數字 5,後面接著零或多個字元的任何字串。If the LIKE '5%' symbol is specified, the Database EngineDatabase 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 子句,例如 WHERE comment LIKE '%30!%%' ESCAPE '!'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 EngineDatabase Engine 會傳回任何含有 30 這個字串的資料列。If ESCAPE and the escape character aren't specified, the Database EngineDatabase 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. 使用 LIKE 搭配 % 萬用字元Using 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 SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

E.E. 使用 LIKE 搭配 % 萬用字元Using 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 LIKE 搭配 % 萬用字元Using 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. 使用 LIKE 搭配 _ 萬用字元Using 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)