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

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

產生資料的語法:

DECLARE @Temp TABLE
(
    Name NVARCHAR(50)
)

INSERT INTO @Temp
    (Name)
VALUES
    (N'aBc'),
    (N'abＣ'),
    (N'ab一'),
    (N'aBZ'),
    (N'aBＺ'),
    (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 和 Ａ 是不同的，指定 _WS 為區分 _WI 為不區分。

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

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

可以看到這個定序是不區分全形和半型的。

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

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

--結果--
Name
----
ab一
aBＺ
a23

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

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

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

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

SELECT * FROM
(
	SELECT 'a' AS W         --小寫A
	UNION ALL SELECT 'A'    --大寫A
	UNION ALL SELECT 'Ａ'   --全形A
	UNION ALL SELECT 'Ｃ'   --全形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
) AS T
ORDER BY W COLLATE Chinese_Taiwan_Stroke_CS_AS_WS
--因為不區分大小寫，會看不太出規律，所以將定序改為區分大小寫

--結果--
 W
----
 a
 A
 Ａ
 c
 C
 Ｃ
 d
 D
 Ｄ

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

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

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

結果

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

--結果--
Name
----
abＣ
ab一
aBＺ
a23

另一個解

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

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

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

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