如何使用 SQL LIKE 操作符進行通配搜索

本文介紹什麼是通配符、如何使用通配符，以及怎樣使用 SQL LIKE 操作符進行通配搜索，以便對數據進行複雜過濾。一、LIKE 操作符前面介紹的所有操作符都是針對已知值進行過濾的。不管是匹配一個值還是多個值，檢驗大於還是小於已知值，或者檢查某個範圍的值，其共同點是過濾中使用的值都是已知的。但是 ...

一、LIKE 操作符
二、使用通配符的技巧
三、小結

本文介紹什麼是通配符、如何使用通配符，以及怎樣使用 SQL LIKE 操作符進行通配搜索，以便對數據進行複雜過濾。

一、LIKE 操作符

前面介紹的所有操作符都是針對已知值進行過濾的。不管是匹配一個值還是多個值，檢驗大於還是小於已知值，或者檢查某個範圍的值，其共同點是過濾中使用的值都是已知的。

但是，這種過濾方法並不是任何時候都好用。例如，怎樣搜索產品名中包含文本 bean bag 的所有產品？用簡單的比較操作符肯定不行，必須使用通配符。利用通配符，可以創建比較特定數據的搜索模式。

在這個例子中，如果你想找出名稱包含 bean bag 的所有產品，可以構造一個通配符搜索模式，找出在產品名的任何位置出現 bean bag 的產品。

通配符（wildcard）

用來匹配值的一部分的特殊字元。

搜索模式（search pattern）

由字面值、通配符或兩者組合構成的搜索條件。

通配符本身實際上是 SQL 的 WHERE 子句中有特殊含義的字元，SQL 支持幾種通配符。為在搜索子句中使用通配符，必須使用 LIKE 操作符。

LIKE 指示 DBMS，後跟的搜索模式利用通配符匹配而不是簡單的相等匹配進行比較。

謂詞（predicate）

操作符何時不是操作符？答案是，它作為謂詞時。

從技術上說，LIKE 是謂詞而不是操作符。雖然最終的結果是相同的，但應該對此術語有所瞭解，以免在 SQL 文獻或手冊中遇到此術語時不知所云。

通配符搜索只能用於文本欄位（字元串），非文本數據類型欄位不能使用通配符搜索。

1.1 百分號（%）通配符

最常使用的通配符是百分號（%）。在搜索串中，% 表示任何字元出現任意次數。

例如，為了找出所有以詞 Fish 起頭的產品，可寫以下的 SELECT 語句：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

輸出：

prod_id     prod_name
-------     ------------------
BNBG01      Fish bean bag toy

此例子使用了搜索模式 'Fish%'。在執行這條子句時，將檢索任意以 Fish 起頭的詞。% 告訴 DBMS 接受 Fish 之後的任意字元，不管它有多少字元。

說明：區分大小寫

根據 DBMS 的不同及其配置，搜索可以是區分大小寫的。如果區分大小寫，則 'fish%' 與 Fish bean bag toy 就不匹配。

通配符可在搜索模式中的任意位置使用，並且可以使用多個通配符。下麵的例子使用兩個通配符，它們位於模式的兩端：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

輸出：

prod_id      prod_name
--------     --------------------
BNBG01       Fish bean bag toy
BNBG02       Bird bean bag toy
BNBG03       Rabbit bean bag toy

搜索模式 '%bean bag%' 表示匹配任何位置上包含文本 bean bag 的值，不論它之前或之後出現什麼字元。

通配符也可以出現在搜索模式的中間，雖然這樣做不太有用。下麵的例子找出以 F 起頭、以 y 結尾的所有產品：

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

提示：根據部分信息搜索電子郵件地址

有一種情況下把通配符放在搜索模式中間是很有用的，就是根據郵件地址的一部分來查找電子郵件，例如 WHERE email LIKE 'b%@forta.com'。

需要特別註意，除了能匹配一個或多個字元外，% 還能匹配 0 個字元。% 代表搜索模式中給定位置的 0 個、1 個或多個字元。

說明：請註意後面所跟的空格

有些 DBMS 用空格來填補欄位的內容。

例如，如果某列有 50 個字元，而存儲的文本為 Fish bean bag toy（17 個字元），則為填滿該列需要在文本後附加 33 個空格。

這樣做一般對數據及其使用沒有影響，但是可能對上述 SQL 語句有負面影響。

子句 WHERE prod_name LIKE 'F%y' 只匹配以 F 開頭、以 y 結尾的 prod_name。

如果值後面跟空格，則不是以 y 結尾，所以 Fish bean bag toy 就不會檢索出來。

簡單的解決辦法是給搜索模式再增加一個 % 號：'F%y%' 還匹配 y 之後的字元（或空格）。更好的解決辦法是用函數去掉空格。請參閱 SQL 如何使用函數處理數據。

註意：請註意 NULL

通配符 % 看起來像是可以匹配任何東西，但有個例外，這就是 NULL。

子句 WHERE prod_name LIKE '%' 不會匹配產品名稱為 NULL 的行。

1.2 下劃線（_）通配符

另一個有用的通配符是下劃線（_）。下劃線的用途與 % 一樣，但它只匹配單個字元，而不是多個字元。

說明：DB2 通配符

DB2 不支持通配符 _。

舉一個例子：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

說明：請註意後面所跟的空格

與上例一樣，可能需要給這個模式添加一個通配符。

輸出：

prod_id      prod_name
--------     --------------------
BR02         12 inch teddy bear
BR03         18 inch teddy bear

這個 WHERE 子句中的搜索模式給出了後面跟有文本的兩個通配符。

結果只顯示匹配搜索模式的行：第一行中下劃線匹配 12，第二行中匹配 18。

8 inch teddy bear 產品沒有匹配，因為搜索模式要求匹配兩個通配符而不是一個。

對照一下，下麵的 SELECT 語句使用 % 通配符，返回三行產品：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';

輸出：

prod_id      prod_name
--------     --------------------
BR01         8 inch teddy bear
BR02         12 inch teddy bear
BNR3         18 inch teddy bear

與 % 能匹配多個字元不同，_ 總是剛好匹配一個字元，不能多也不能少。

1.3 方括弧（[]）通配符

方括弧（[]）通配符用來指定一個字元集，它必須匹配指定位置（通配符的位置）的一個字元。

說明：並不總是支持集合

與前面描述的通配符不一樣，並不是所有 DBMS 都支持用來創建集合的 []。

微軟的 SQL Server 支持集合，但是 MySQL，Oracle，DB2，SQLite 都不支持。為確定你使用的 DBMS 是否支持集合，請參閱相應的文檔。

例如，找出所有名字以 J 或 M 起頭的聯繫人，可進行如下查詢：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

輸出：

cust_contact
-----------------
Jim Jones
John Smith
Michelle Green

此語句的 WHERE 子句中的模式為 '[JM]%'。這一搜索模式使用了兩個不同的通配符。[JM] 匹配方括弧中任意一個字元，它也只能匹配單個字元。因此，任何多於一個字元的名字都不匹配。[JM]之後的 % 通配符匹配第一個字元之後的任意數目的字元，返回所需結果。

此通配符可以用首碼字元 ^（脫字型大小）來否定。例如，下麵的查詢匹配以 J 和 M 之外的任意字元起頭的任意聯繫人名（與前一個例子相反）：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

當然，也可以使用 NOT 操作符得出類似的結果。^ 的唯一優點是在使用多個 WHERE 子句時可以簡化語法：

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

二、使用通配符的技巧

正如所見，SQL 的通配符很有用。但這種功能是有代價的，即通配符搜索一般比前面討論的其他搜索要耗費更長的處理時間。這裡給出一些使用通配符時要記住的技巧。

不要過度使用通配符。如果其他操作符能達到相同的目的，應該使用其他操作符。
在確實需要使用通配符時，也儘量不要把它們用在搜索模式的開始處。把通配符置於開始處，搜索起來是最慢的。
仔細註意通配符的位置。如果放錯地方，可能不會返回想要的數據。

總之，通配符是一種極其重要和有用的搜索工具，以後我們經常會用到它。

三、小結

本文介紹了什麼是通配符，如何在 WHERE 子句中使用 SQL 通配符，還說明瞭通配符應該細心使用，不要使用過度。

原文鏈接：https://developerastrid.com/sql/sql-like/

（完）