关于sqlserver字符类型查询条件区分大小写

Posted by

在写sql的查询时 如下:

前段时间测试Job时,在SQL中出现了个 “无法解决 equal to
操作的排序规则冲突”,百度了一下,才发现是两个表之间元素创建时排序规则不同,一个是collate
Chinese_PRC_CI_AI_WS,一个为COLLATE
SQL_Latin1_General_CP1_CI_AS。

 select * from Users where username=’WangE’

顺便引用下文章作参考:“无法解决 equal to
操作的排序规则冲突。”的错误分析和解决方法

 select * from Users where username=’wange’

SQL SERVER的排序规则平时使用不是很多,也许不少初学者还比较陌生,但有
一个错误大家应是经常碰到: SQL
SERVER数据库,在跨库多表连接查询时,若两数据
库默认字符集不同,系统就会返回这样的错误:

上面两条查询语句的结果是一样的,这说明username的条件并没有区分大小写。

“无法解决 equal to 操作的排序规则冲突。”

 

一.错误分析:
  这个错误是因为排序规则不一致造成的,我们做个测试,比如:
create table #t1(
name varchar(20) collate Albanian_CI_AI_WS,
value int)

若要使用上面的的语句能够执行按区分大小写查询 需要在查询的结尾加上
collate Chinese_PRC_CS_AI_WS

create table #t2(
name varchar(20) collate Chinese_PRC_CI_AI_WS,
value int )

完整语句是:

表建好后,执行连接查询:

select * from Users where username=’WangE’ collate
Chinese_PRC_CS_AI_WS

select * from #t1 A inner join #t2 B on A.name=B.name

这样,错误就出现了:

服务器: 消息 446,级别 16,状态 9,行 1
无法解决 equal to 操作的排序规则冲突。
  要排除这个错误,最简单方法是,表连接时指定它的排序规则,这样错误就
不再出现了。语句这样写:

select *
from #t1 A inner join #t2 B
on A.name=B.name collate Chinese_PRC_CI_AI_WS

二.排序规则简介:

什么叫排序规则呢?MS是这样描述的:”在 Microsoft SQL Server 2000 中,
字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存
储和比较字符所使用的规则。”
  在查询分析器内执行下面语句,可以得到SQL SERVER支持的所有排序规则。

    select * from ::fn_helpcollations()

排序规则名称由两部份构成,前半部份是指本排序规则所支持的字符集。
如:
  Chinese_PRC_CS_AI_WS
前半部份:指UNICODE字符集,Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义:
  _BIN 二进制排序
  _CI(CS) 是否区分大小写,CI不区分,CS区分
  _AI(AS) 是否区分重音,AI不区分,AS区分   
  _KI(KS) 是否区分假名类型,KI不区分,KS区分 
_WI(WS) 是否区分宽度 WI不区分,WS区分 

区分大小写:如果想让比较将大写字母和小写字母视为不等,请选择该选项。
区分重音:如果想让比较将重音和非重音字母视为不等,请选择该选项。如果选择该选项,
比较还将重音不同的字母视为不等。
区分假名:如果想让比较将片假名和平假名日语音节视为不等,请选择该选项。
区分宽度:如果想让比较将半角字符和全角字符视为不等,请选择该选项

三.排序规则的应用:
  SQL
SERVER提供了大量的WINDOWS和SQLSERVER专用的排序规则,但它的应用往往
被开发人员所忽略。其实它在实践中大有用处。

  例1:让表NAME列的内容按拼音排序:

create table #t(id int,name varchar(20))
insert #t select 1,’中’
union all select 2,’国’
union all select 3,’人’
union all select 4,’阿’

select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS
drop table #t
/*结果:
id name


4 阿
2 国
3 人
1 中
*/

  例2:让表NAME列的内容按姓氏笔划排序:

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注