c# - 为什么 TSQL 将 "ｓｏｆｉａ"视为与 "sofia"相同？这是什么字符串编码？

Question

我遇到了一个案例，SQL 服务器可以将“ｓｏｆia”和“sofia”存储为两个不同的字符串，但是在 TSQL 中进行比较时，无论使用 COLLATE，即使是二进制整理，它们都是相同的:

CREATE TABLE #R (NAME NvarchAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #R VALUES (N'sofia')
INSERT INTO #r VALUES (N'ｓｏｆｉａ')

SELECT * FROM #r WHERE NAME = N'ｓｏｆｉａ'

sofia
ｓｏｆｉａ

(2 row(s) affected)

IF 'ｓｏｆｉａ' = 'sofia'  COLLATE SQL_Latin1_General_CP1_CI_AS 
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

-------------------
Values are the same

(1 row(s) affected)

IF 'ｓｏｆｉａ' = 'sofia'  COLLATE SQL_Latin1_General_CP437_BIN
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

-------------------
Values are the same

(1 row(s) affected)

I tried to find out the encode of "ｓｏｆｉａ"

http://stackoverflow.com/questions/1025332/determine-a-strings-encoding-in-c-sharp

It said:

            // If all else fails, the encoding is probably (though certainly not
            // definitely) the user's local codepage! One might present to the user a
            // list of alternative encodings as shown here: http://stackoverflow.com/questions/8509339/what-is-the-most-common-encoding-of-each-language
            // A full list can be found using Encoding.GetEncodings();

I iterate through all the encoding returned from Encoding.GetEncodings(), none of them match

Looking into the binary I found an interesting fact: “ｓｏｆｉａ” itself is encoded with UTF16, but it can be generated from  "SOFIA" UTF16 by filling “1” instead of “0” in the extra byte besides ASCII code (Ex for ‘S’: 83 255 vs 83 0)  It is shown as lower case. In C#, 

“ｓｏｆｉａ”

                             [0]         83          byte                                    
                             [1]         255        byte
                             [2]         79          byte
                             [3]         255        byte
                             [4]         70          byte
                             [5]         255        byte
                             [6]         73          byte
                             [7]         255        byte
                             [8]         65          byte
                             [9]         255        byte

"SOFIA"

                             [0]         83          byte                                    
                             [1]         0        byte
                             [2]         79          byte
                             [3]         0        byte
                             [4]         70          byte
                             [5]         0        byte
                             [6]         73          byte
                             [7]         0        byte
                             [8]         65          byte
                             [9]         0        byte

"sofia"

                             [0]         115          byte                                    
                             [1]         0        byte
                             [2]         79          byte
                             [3]         0        byte
                             [4]         70          byte
                             [5]         0        byte
                             [6]         105          byte
                             [7]         0        byte
                             [8]         97          byte
                             [9]         0        byte

One can create two different directorie/files with name as C:\ｓｏｆｉａ\, C:\sofia\ or  ｓｏｆｉａ.txt, sofia.txt.

Why does the SQL engine think they are the same while storing them with the original streams?

In order to get just the exact I want I had to convert to binary first:

SELECT * FROM #r WHERE CONVERT(VARBINARY(100), Name) = CONVERT(VARBINARY(100), N'ｓｏｆｉａ')

ｓｏｆｉａ

(1 row(s) affected)

SELECT * FROM #r WHERE CONVERT(VARBINARY(100), Name) = CONVERT(VARBINARY(100), N'sofia')

sofia

(1 row(s) affected)

但这有很多副作用，比如文化和大小写。我如何教 TSQL 引擎知道它们的不同之处而不需要太多成本？

这种字符串编码有正式名称吗？

Answer 1

这里有两个问题。

首先:排序问题。排序规则定义字符的排序和相等性。正如@Kazetsukai 所建议的那样，在此处提供帮助的特定排序规则属性是宽度敏感度。但是，您不能简单地添加 _WS任何排序规则名称并假定它将是一个有效的排序规则。事实上，SQL_Latin1_General_CP1_CI_AS_WS不是有效的排序规则。

您可以通过 SELECT * FROM fn_helpcollations() WHERE [name] LIKE N'latin%[_]ws'; 获得一组有限的排序规则.该查询的结果表明您可能需要的排序规则是 Latin1_General_CI_AS_WS .以及任何以 _BIN2 结尾的归类会起作用(尽量不要使用以 _BIN 结尾的排序规则，因为它们已被弃用，就像以 SQL_ 开头的排序规则一样)。

但是，出于某种原因，即使使用它们似乎也不起作用:

IF 'ｓｏｆｉａ' = 'sofia' COLLATE Latin1_General_CI_AS_WS
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

IF 'ｓｏｆｉａ' = 'sofia' COLLATE Latin1_General_BIN2
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

两者的结果都是“值相同”。这将我们带到:

第二:使用 NVARCHAR 时¹ 数据，您必须使用大写前缀字符串文字 N , 否则它会将字符隐式转换为它们各自的 VARCHAR ² 个字符在前面(如果 Unicode 代码点与字段或操作的排序规则指定的代码页中存在的字符之间没有定义映射，则字符转换为 ?)。

IF N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_CI_AS_WS
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

IF N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_BIN2
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

用 N 作为这些字面值的前缀允许预期的行为，两个查询的结果现在是“值不同”。

---

¹ XML和 N -prefixed 类型将数据存储为 UTF-16 Little Endian。默认处理只是 UCS-2/Base Multilingual Plane (BMP) 字符。但是，如果使用以 _SC 结尾的排序规则, 然后它可以正确处理带有补充字符的完整 UTF-16。

² CHAR , VARCHAR , 和 TEXT (但不要使用最后一个，因为它已被弃用)类型是使用代码页扩展的 8 位 ASCII。