如何在sql中对与字母数字值混合的数值进行排序 [英] How to Sort numeric values mixed with alphanumeric values in sql
问题描述
大家好,
我必须对列进行排序,其值包含
AZC1
AZC10
AZC12
AZC100
AZC2
AZC24
AZC3
AZC34
我要求的输出是
AZC1
AZC2
AZC3
AZC10
AZC12
AZC24
AZC34
AZC100
(即)它应首先对Alpha值进行排序,然后对numeris值进行排序。数值应该像1,2,3,10,24那样排序....
参考:在Sql中排序AlphaNumeric数据 [ ^ ]
ok我给出了一个简单的解决方案
选择 * 来自 test
order by substring(Zone, 1 , 3 ),
cast(STuff(区域, 1 , 3 ,' 000') as int )
ok这是一个复杂的
<前lang = sql> 选择 * 来自 test
order by substring(Zone, 1 ,patindex(' %[0-9]%',区域)-1),
cast(STuff(区域, 1 ,patindex(' %[0-9]%' ,zone)-1,' 000') as int )
如果你想检查它
使用这个 Sql Fiddle [ ^ ]
Hi All,
I have to sort a column contains value like
AZC1
AZC10
AZC12
AZC100
AZC2
AZC24
AZC3
AZC34
My required output is
AZC1
AZC2
AZC3
AZC10
AZC12
AZC24
AZC34
AZC100
(ie) It should sort Alpha values first and then numeris values. Numeric values should be sorted like 1,2,3,10,24....
Refer: Sort AlphaNumeric Data in Sql[^]
ok i gave a simple solution
select * from test order by substring(Zone,1,3) , cast(STuff(Zone,1,3,'000') as int)
ok here is a complex one
select * from test order by substring(Zone,1,patindex('%[0-9]%',zone)-1) , cast(STuff(Zone,1,patindex('%[0-9]%',zone)-1,'000') as int)
if you want to check it
use this Sql Fiddle[^]
这篇关于如何在sql中对与字母数字值混合的数值进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!