请大家帮忙，关于数据库排序问题？

[chen7872]

根据一个字段（NO）排序，数据如下：
G101、G12、G23、G16、G23A、G12A、G206、G1、G206A
排列顺序如下：
G1、G12、G12A、G16、G23、G23A、G101、G206、G206A
请问如何写SQL进行排序？

[pengdali]

select * from(
select no='G101'
union all select 'G12'
union all select 'G23'
union all select 'G16'
union all select 'G23C'
union all select 'G23B'
union all select 'G23A'
union all select 'G12A'
union all select 'G206'
union all select 'G1'
union all select 'G206A'
) a order by

substring(no,2,

case when PATINDEX('%[^0123456789]%',substring(no,2,8000))=0
then 8000 else PATINDEX('%[^0123456789]%',substring(no,2,8000))-1 end)+0

,no

[pbsql]

越搞越复杂了

去掉G、A，然后转换为整形排序：
...order by convert(int,replace(replace(NO,'G',''),'A','')),NO

[pengdali]

如果他不只是A，如果有
G23B
G23C
呢？

[zjcxc]

大力的更具有通用性.

[pbsql]

去掉G、A，然后转换为整形排序：
order by convert(int,replace(replace(NO,'G',''),'A',''))

[zjcxc]

select * from 表 order by cast(replace(replace(NO,'G',''),'A','') as int)

[zjcxc]

上面的还错了一点,应该改为:
select * from 表 order by cast(replace(replace(NO,'G',''),'A','') as int),no

[zjcxc]

--下面是数据测试
select * from(
select no='G101'
union all select 'G12'
union all select 'G23'
union all select 'G16'
union all select 'G23A'
union all select 'G12A'
union all select 'G206'
union all select 'G1'
union all select 'G206A'
) a order by cast(replace(replace(NO,'G',''),'A','') as int),no

--结果:
no
-----
G1
G12
G12A
G16
G23
G23A
G101
G206
G206A

（所影响的行数为 9 行）

[wzh1215]

应该是去掉前面的G就行了：
order by replace(NO,'G','')

[wzh1215]

还是zjcxc(邹建）的正确！

[txlicenhe]

order by cast(replace(replace(NO,'G',''),'A','') as int)