对sql2000 的查询结果按ascii码的大小进行排序

[coolfeng123]

例如两个值00053和0005-4，对他们进行排序结果按ascii的大小排序，结果应该为
0005-4
00053
但是sql里总是00053在前面
不想使用修改排序规则这个方法，有没有纯用语句的方法？

[ojuju10]

create table #(name varchar(10))
insert into # select '0005-4'
insert into # select '00053'

select * from #
/*
name
----------
0005-4
00053
*/
select * from #
order by name 
/*
name
----------
00053
0005-4
*/
select * from #
order by ascii(name)  

/*
name
----------
0005-4
00053
*/

[coolfeng123]

[Quote=引用 5 楼 ojuju10 的回复:]
SQL code

create table #(name varchar(10))
insert into # select '0005-4'
insert into # select '00053'

select * from #
/*
name
----------
0005-4
00053
*/
select * from #
order by name
/*
name
----------
00053
0005-4
*/
select * from #
order by ascii(name)

/*
name
----------
0005-4
00053
*/
[/Quote]


兄台，你在数据插入数据的时候把00053放到前面，你就知道你的这个结果是错的了

[fcuandy]

select ascii('-'),ascii('4')

DECLARE @t TABLE(v VARCHAR(100))
INSERT @t SELECT '00534'
UNION ALL SELECT '0053-4'

SELECT * FROM @t ORDER BY v

SELECT * FROM @t ORDER BY v COLLATE Albanian_BIN

[coolfeng123]

[Quote=引用 7 楼 fcuandy 的回复:]
SQL codeselect ascii('-'),ascii('4')

DECLARE @t TABLE(v VARCHAR(100))
INSERT @t SELECT '00534'
UNION ALL SELECT '0053-4'

SELECT * FROM @t ORDER BY v

SELECT * FROM @t ORDER BY v COLLATE Albanian_BIN
[/Quote]
正解

[viva369]

select * from tablename order by checksum(colunname)

[viva369]

看错
select * from tablename order by ascii(colunname)

[coolfeng123]

你这个只能适用于位数为1的字符串

[jinjazz]

减号是比数字小