--下面是数据测试
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
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