varchar()和nvarchar()之间奇怪的SQL Server 2005排序规则差异 [英] Weird SQL Server 2005 Collation difference between varchar() and nvarchar()
问题描述
有人可以解释一下吗?
SELECT
CASE WHEN CAST('iX' AS nvarchar(20))
> CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END,
CASE WHEN CAST('iX' AS varchar(20))
> CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END
结果: 0 1
SELECT
CASE WHEN CAST('i' AS nvarchar(20))
> CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END,
CASE WHEN CAST('i' AS varchar(20))
> CAST('-' AS varchar(20)) THEN 1 ELSE 0 END
结果: 1 1
在第一个查询中, nvarchar()
结果不是我想要的期望,但又删除了 X
使 nvarchar()
排序按预期发生。
On the first query, the nvarchar()
result is not what I'm expecting, and yet removing the X
make the nvarchar()
sort happen as expected.
(我的原始查询使用''
和 N''
文字语法来区分 varchar()
和 nvarchar()
而不是 CAST()
,并且得到相同的结果。)
(My original queries used the ''
and N''
literal syntax to distinguish varchar()
and nvarchar()
rather than CAST()
and got the same result.)
数据库的排序规则设置为 SQL_Latin1_General_CP1_CI_AS
。
Collation setting for the database is SQL_Latin1_General_CP1_CI_AS
.
推荐答案
比较unicode文本时,连字符会被特殊处理。 Unicode比较使用字典顺序,它忽略连字符。非unicode文本比较不是这种情况。
When comparing unicode text, hyphens are treated specially. Unicode comparison uses "dictionary order", which ignores hyphens. This is not the case with non-unicode text comparison.
比较 -X
和 iX
,就像比较 X
和 iX
,所以-左侧的X
更大。
比较-和 i时,就像比较和 i,所以 i的右边更大。
Comparing -X
and iX
, is like comparing X
and iX
, so -X
, the left side, is greater.
When comparing "-" and "i", is like comparing "" and "i", so "i", the right side is greater.
从MSDN,
SQL排序规则对
非Unicode数据进行排序与
不兼容,由
Microsoft Windows操作系统
提供;但是,
Unicode数据的排序与Windows
排序规则的
特定版本兼容。因为用于非Unicode和Unicode数据
的比较
规则不同,所以当您使用SQL
归类时,对于相同的
的比较可能会看到不同的
结果。字符,具体取决于
基础数据类型。例如,如果
使用的是SQL排序规则
SQL_Latin1_General_CP1_CI_AS,则
非Unicode字符串'a-c'小于
字符串'ab',因为连字符
(-)被排序为在 b之前的单独
字符。
但是,如果将这些字符串
转换为Unicode并执行相同的
比较,则认为Unicode字符串N'a-c'
大于N'ab '
,因为Unicode排序规则使用
a word sort忽略连字符。
A SQL collation's rules for sorting non-Unicode data are incompatible with any sort routine that is provided by the Microsoft Windows operating system; however, the sorting of Unicode data is compatible with a particular version of the Windows sorting rules. Because the comparison rules for non-Unicode and Unicode data are different, when you use a SQL collation you might see different results for comparisons of the same characters, depending on the underlying data type. For example, if you are using the SQL collation "SQL_Latin1_General_CP1_CI_AS", the non-Unicode string 'a-c' is less than the string 'ab' because the hyphen ("-") is sorted as a separate character that comes before "b". However, if you convert these strings to Unicode and you perform the same comparison, the Unicode string N'a-c' is considered to be greater than N'ab' because the Unicode sorting rules use a "word sort" that ignores the hyphen.
SELCT正文从MSDN_Articles的URL输入
( http:// support。 microsoft.com/kb/322112 )
这篇关于varchar()和nvarchar()之间奇怪的SQL Server 2005排序规则差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!