varchar 类型字段排序

[guoke1]

字段类型 varchar
字段值
AL1
AL28
AL3
AL41
AL5
AL33
AL66
AL100
AL376


想让字段值按从小到大排序

AL1
AL3
AL5
AL28
AL33
AL41
AL66
AL100
AL376


select * from table where 1=1 order by Specimen+0
select * from table where 1=1 order by Specimen*1
CAST和CONVERT
都试过，实现不了，请高手支个招。

[lxq19851204]

select * from table where 1=1 order by replace(Specimen,"AL","")+ 0

[ACMAIN_CHM]

order by mid(Specimen,2)+0

[rucypli]

[Quote=引用 1 楼 lxq19851204 的回复:]
select * from table where 1=1 order by replace(Specimen,"AL","")+ 0
[/Quote]1

[WWWWA]

如果格式是固定的
select * from table where 1=1 order by replace(Specimen,"AL","0")+ 0