varchar()和nvarchar()之间奇怪的SQL Server 2005排序规则差异 [英] Weird SQL Server 2005 Collation difference between varchar() and nvarchar()

查看:122
本文介绍了varchar()和nvarchar()之间奇怪的SQL Server 2005排序规则差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有人可以解释一下吗?

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆