iT邦幫忙

8

[MS SQL] 找出大小寫字母之外的其他文字 (包含全形)

這是最近碰到的問題,要找出那些資料有出現大小寫字母之外的其他文字,字母不包含全形,舉例如下:

資料 是否需找出 說明
aBc X 只包含字母
abC O 包含全形字母
ab一 O 包含中文字
aBZ X 只包含字母
aBZ O 包含全形字母
a23 O 包含數字

看到這裡有興趣的大大可以先不往下看,實際查詢看看。

產生資料的語法:

DECLARE @Temp TABLE
(
    Name NVARCHAR(50)
)

INSERT INTO @Temp
    (Name)
VALUES
    (N'aBc'),
    (N'abC'),
    (N'ab一'),
    (N'aBZ'),
    (N'aBZ'),
    (N'a23')

這題的問題點在全形字上,想了很久百思不得其解,最後的結果讓我重新認識了 SQL。

首先看到要查字母想到可以用 LIKE 搭配萬用字元如下。

SELECT * 
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%'

--結果--
Name
----
ab一
a23 

不過發現全形字母沒有被找出來,猜想可能是資料庫定序的問題,所以去查了一下資料庫預設的定序是 Chinese_Taiwan_Stroke_CI_AS,這裡稍微說明一下定序的規則。

定序的規則

  • Case sensitivity:
    區分大小寫,例如 A 和 a 是不同的,指定 _CS 為區分 _CI 為不區分。
  • Accent sensitivity:
    區分腔調,例如 a 和 á 是不同的,指定 _AS 為區分 _AI 為不區分。
  • Kana Sensitivity:
    區分日文的片假名和平假名,指定 _KS 為區分 _KI 為不區分。
  • Width sensitivity:
    區分半形和全形,例如 A 和 A 是不同的,指定 _WS 為區分 _WI 為不區分。

要查詢定序的詳細資訊可以下這段語法

SELECT name, description FROM fn_helpcollations()
WHERE name LIKE 'Chinese_Taiwan%'

可以看到這個定序是不區分全形和半型的。
https://ithelp.ithome.com.tw/upload/images/20180618/20106865pn9zaLjwLU.jpg

因此我想定序加上 WS 應該就可以順利找出全形字了吧,修改後如下。

SELECT * 
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%' COLLATE Chinese_Taiwan_Stroke_CI_AS_WS 

--結果--
Name
----
ab一
aBZ
a23

很神奇的全形 Z 被找出來了,可是全形 C 卻沒有,因此我又另外做了一個測試。

SELECT COUNT(*) FROM @Temp Where 'C' LIKE '%[^A-C]%' 
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS --等於 6
SELECT COUNT(*) FROM @Temp Where 'C' LIKE '%[^A-D]%' 
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS --等於 0

第一段語法 '%[^A-C]%' 如果全形字剛好在區間末端,可以正確判斷出,可第二段語法 '%[^A-D]%' 卻不能,首先想到這是不是 SQL LIKE 的某些限制,可是又不太像,這裡我卡了非常久,就是想不出為什麼。
/images/emoticon/emoticon20.gif

經過一陣掙扎後,靈光一閃想說將字母排序看看,結果真的讓我看出了端倪。

SELECT * FROM
(
	SELECT 'a' AS W         --小寫A
	UNION ALL SELECT 'A'    --大寫A
	UNION ALL SELECT 'A'   --全形A
	UNION ALL SELECT 'C'   --全形C
	UNION ALL SELECT 'c'    --小寫C 
	UNION ALL SELECT 'C'    --大寫C
	UNION ALL SELECT 'd'    --小寫D
	UNION ALL SELECT 'D'    --大寫D
	UNION ALL SELECT 'D'   --全形D
) AS T
ORDER BY W COLLATE Chinese_Taiwan_Stroke_CS_AS_WS
--因為不區分大小寫,會看不太出規律,所以將定序改為區分大小寫

--結果--
 W
----
 a
 A
 A
 c
 C
 C
 d
 D
 D

可以發現字母的排序並不是像 ASCII 碼,將小寫、大寫、全形各放在不同區域,而是會將同字母放在一起,並按照 小寫->大寫->全形的順序排序,因此我大膽猜想 LIKE 和一般程式語言的正規式不太一樣,字母區間是以定序的排序為依據。

所以才會有上面測試的結果,因為 全形C 排在 大寫 C 之後,剛好不在 A-C 區間,而 全形C大寫D 之前,所以第二個查詢才會沒有被判斷出來。

既然知道問題點,就知道要怎麼解了,定序除了上面四種規則之外還有特殊的 二進位定序,此定序規則會按照字碼做排序,如果欄位型態是 VARCHAR 會以地區的 ANSI 字碼排序,如果欄位是 NVARCHAR 會以 Unicode 字碼排序,因此利用以字碼排序的特性,就可以將 小寫大寫全形 三者完全分開了。

結果

SELECT * 
FROM @Temp
WHERE Name LIKE '%[^A-Za-z]%' COLLATE Chinese_Taiwan_Stroke_BIN2

--結果--
Name
----
abC
ab一
aBZ
a23

另一個解

SELECT * 
FROM @Temp 
WHERE Name LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' 
COLLATE Chinese_Taiwan_Stroke_CI_AS_WS

在思考過程中還有發現另一個解,雖然比較笨但還是可以解決問題,不過總覺得這樣沒有弄清楚問題的根本,渾身不自在的,哈哈哈。
/images/emoticon/emoticon70.gif

今天就到這裡了,感謝各位大大觀看。

參考文章
Collation and Unicode Support
[SQL Server]談談SQL Server的定序(Collation)
[SQL]中文字排序和過濾的問題處理
[SQL Server] SQL Server儲存Unicode補充字集(Supplementary Character)


2 則留言

0
純真的人
iT邦高手 4 級 ‧ 2018-06-18 20:18:21

哈~這篇知識可以學更多呢~

其實他的小寫->大寫->全形跟網頁的特殊字帶出的方式一樣呢~

你去Google研究a【a】的號碼順序就知道了~

看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 5 級‧ 2018-06-18 21:17:28 檢舉

學到很多 +1

這一篇真的很猛,
我完全沒有考慮過全形/images/emoticon/emoticon06.gif

fysh711426 iT邦研究生 5 級‧ 2018-06-19 22:37:53 檢舉

純真的人 哈哈哈,感謝大大,
不過您說的 特殊字帶出的方式一樣 是只 小寫->大寫->全形 這個順序嗎?

fysh711426 iT邦研究生 5 級‧ 2018-06-19 22:38:08 檢舉

暐翰 大大的文章也讓我學到很多。 /images/emoticon/emoticon41.gif

fysh711426 iT邦研究生 5 級‧ 2018-06-19 22:48:53 檢舉

神Q超人在遇到這個問題之前,我也沒有特別注意這些小細節,
所以才會覺得重新認識了 SQL。
/images/emoticon/emoticon37.gif

fysh711426
說錯他是大寫→小寫→其他組合字
因為是按照 ASCII 的數字排列到上萬~
以前玩聊天室~就是用ASCII來突破封鎖字的限制@@

fysh711426 iT邦研究生 5 級‧ 2018-06-20 00:09:34 檢舉

恩對,如果是二進制定序就會和 ASCII 順序一樣,用字碼排列,而一般的定序就不是單純用字碼,大順序會是這樣:

  1. NULL
  2. 特殊字
  3. 數字
  4. 字母
  5. 中文字

更詳細的資訊可以到這裡看,定序的排序圖。
Collation chart for 0409.1252.Latin1_General_CS_AS

然後原來 ASCII 可以用來破封鎖字阿,哈哈哈。
/images/emoticon/emoticon16.gif

0
純真的人
iT邦高手 4 級 ‧ 2018-06-19 14:58:01

哈...我多查了一點資料@@..

參考:
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017

這方式也是差不多@@..

DECLARE @Temp TABLE
(
    Name NVARCHAR(50)
)

INSERT INTO @Temp
    (Name)
VALUES
    (N'aBc'),
    (N'abC'),
    (N'ab一'),
    (N'aBZ'),
    (N'aBZ'),
    (N'a23')

select *
from @Temp
where PATINDEX('%[^A-z]%' COLLATE Chinese_Taiwan_Stroke_BIN2,Name) > 0
fysh711426 iT邦研究生 5 級‧ 2018-06-19 22:54:00 檢舉

感謝大大分享,原來函數裡面可以放定序,PATINDEX 第一次看到,趕快筆記起來。
/images/emoticon/emoticon41.gif

我要留言

立即登入留言