sql对数字和字母组合字段根据数字排序
数据类似1,3,2H,100CD,12K,10 数字的长度不固定,想要的结果是1,2H,3,10,12K,100CD
[最优解释]
--单纯的数字在前，字母在后是比较好处理的
create table T (col varchar(10))
insert into T values(1)
insert into T values(3)
insert into T values('2H')
insert into T values('100CD')
insert into T values('12K')
insert into T values(10)
select
col,
convert( int,
case when patindex('%[^0-9]%',col) >0
then substring(col,1, patindex('%[^0-9]%',col) -1 )
else col
end
) as new_number
from T
order by 2
/**
col new_number
11
2H2
33
1010
12K12
100CD100
**/
drop table T
