英文数字排序问题

[e8book]

RoomID
A1-1001
A1-1002
A1-1003
A1-1004
A1-1005
A1-101
A1-102
A1-103
A1-104
A1-105

使用select * from RoomTable order by RoomID得到上面的列表，如果我想需要能按数字大小的排序如：
A1-101
A1-102
A1-103
A1-104
A1-105
A1-1001
A1-1002
A1-1003
A1-1004
A1-1005
我要怎么写呢？谢谢。

[e8book]

如果是数据量比较大时，用零时表会不会耗费不起。

[e8book]

SELECT * FROM @T
ORDER BY
CAST(SUBSTRING(ID,4,4) AS INT)

substring(roomid,3,len(roomid)-3)

roomid是不确定前面的英文位数的，这样做不太行得通。

[e8book]

SQL没有像javascript的parseInt的方法。

[paoluo]

select * from RoomTable
order by Cast(Stuff(RoomID,1,CharIndex('-',RoomID),'') As Int)

[paoluo]

還可以這樣

Select *From RoomTable Order By Len(RoomID),RoomID

[e8book]

谢谢，有些出入，不过按照你的思路我已经找到解决方法了，结贴。

[liangpei2008]

DECLARE @T TABLE(ID VARCHAR(50))
INSERT @T SELECT 'A1-1001'
UNION ALL SELECT 'A1-1002'
UNION ALL SELECT 'A1-1003'
UNION ALL SELECT 'A1-1004'
UNION ALL SELECT 'A1-1005'
UNION ALL SELECT 'A1-101'
UNION ALL SELECT 'A1-102'
UNION ALL SELECT 'A1-103'
UNION ALL SELECT 'A1-104'
UNION ALL SELECT 'A1-105'

SELECT * FROM @T
ORDER BY
CAST(SUBSTRING(ID,4,4) AS INT)

[OracleRoob]

create table #t(roomid varchar(100))

insert into #t
select 'A1-1001' union all
select 'A1-1002' union all
select 'A1-1003' union all
select 'A1-1004' union all
select 'A1-1005' union all
select 'A1-101' union all
select 'A1-102' union all
select 'A1-103' union all
select 'A1-104' union all
select 'A1-105'


select *
from #t
order by substring(roomid,3,len(roomid)-3)

drop table #t