按字符串排序时,如何实现按区别大小写呢?
以下代码,为何顺序中,会混合大小写呢?我认为大写字母应该排序小写字母前呀,
因为我没有改过排序规则呀,默认是字典排序吧。A应该小于a吧。但是实际排序结果却是混在一块的。
如何实现排序时,区分大小写呢?create table #t1 (f1 varchar(5),f2 varchar(10))insert #t1
select 'Y','' union all
select 'w','' union all
select 'z','' union all
select 'A','' union all
select 'a','' union all
select 'A','' union all
select 'B','' union all
select 'b','' union all
select 'c','' union all
select 'C','' union all
select 'D','' union all
select 'd',''select *
from #t1
order by f1/*
f1 f2
----- ----------
A
a
A
B
b
c
C
D
d
w
Y
z (所影响的行数为 12 行)
*/
以下代码,为何顺序中,会混合大小写呢?我认为大写字母应该排序小写字母前呀,
因为我没有改过排序规则呀,默认是字典排序吧。A应该小于a吧。但是实际排序结果却是混在一块的。
如何实现排序时,区分大小写呢?create table #t1 (f1 varchar(5),f2 varchar(10))insert #t1
select 'Y','' union all
select 'w','' union all
select 'z','' union all
select 'A','' union all
select 'a','' union all
select 'A','' union all
select 'B','' union all
select 'b','' union all
select 'c','' union all
select 'C','' union all
select 'D','' union all
select 'd',''select *
from #t1
order by f1/*
f1 f2
----- ----------
A
a
A
B
b
c
C
D
d
w
Y
z (所影响的行数为 12 行)
*/
select 'Y','' union all
select 'w','' union all
select 'z','' union all
select 'A','' union all
select 'a','' union all
select 'A','' union all
select 'B','' union all
select 'b','' union all
select 'c','' union all
select 'C','' union all
select 'D','' union all
select 'd',''select *
from #t1
order by f1/*
f1 f2
----- ----------
a
A
A
b
B
c
C
d
D
w
Y
z (所影响的行数为 12 行)
*/
from #t1
order by ascii(f1)-ascii('a')
select 'Y','' union all
select 'w','' union all
select 'z','' union all
select 'A','' union all
select 'a','' union all
select 'A','' union all
select 'B','' union all
select 'b','' union all
select 'c','' union all
select 'C','' union all
select 'D','' union all
select 'd',''select *
from #t1
order by ascii(f1)/*
f1 f2
----- ----------
A
A
B
C
D
Y
a
b
c
d
w
z (12 行受影响)
*/
select 'Y','' union all
select 'w','' union all
select 'z','' union all
select 'A','' union all
select 'a','' union all
select 'A','' union all
select 'B','' union all
select 'b','' union all
select 'c','' union all
select 'C','' union all
select 'D','' union all
select 'd',''select *
from #t1
order by f1 COLLATE Chinese_Prc_Bin
DROP TABLE #t1
from #t1
order by f1 COLLATE Chinese_Prc_Bin