本文介紹什麼是通配符、如何使用通配符,以及怎樣使用 SQL 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/
(完)