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

这是最近碰到的问题，要找出那些资料有出现大小写字母之外的其他文字，字母不包含全形，举例如下:

资料是否需找出说明aBcX只包含字母abＣO包含全形字母ab一O包含中文字aBZX只包含字母aBＺO包含全形字母a23O包含数字

看到这里有兴趣的大大可以先不往下看，实际查询看看。

产生资料的语法:

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 @TempWHERE 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 @TempWHERE 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 --等于 6SELECT 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         --小写AUNION ALL SELECT 'A'    --大写AUNION ALL SELECT 'Ａ'   --全形AUNION ALL SELECT 'Ｃ'   --全形CUNION ALL SELECT 'c'    --小写C UNION ALL SELECT 'C'    --大写CUNION ALL SELECT 'd'    --小写DUNION ALL SELECT 'D'    --大写DUNION ALL SELECT 'Ｄ'   --全形D) AS TORDER 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 @TempWHERE 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)