SQL语句的问题：字符串型的字段，按要求排序返回的问题

[jobuson]

现有varchar(50)类型的字段buildingNum，
其中值大多是“1”“2”“23”之类的楼号，
不过也有的楼号是“10A”“10B”之类，
若简单地按该字段排序（其实就是字典排序）返回:
select buildingNum from Table order by buildingNum;
--------------------------------------------------
buidlingNum
1
10A
10B
2
23
--------------------------------------------------

而我希望排成这样：
1
2
10A
10B
23
这样楼号按低到高排，很清楚。


有没有办法。？？

[OpenVMS]

select buildingNum from Table order by left(buildingNum,2)

[j9988]

如果仅是如：3A 3B 3C 10A 10B 25 不出现如105A 之类的试一下：
select buildingNum from tab order by
case isnumeric(left(buildingNum,2)) when 1 then cast(left(buildingNum,2) as int)
else cast(left(buildingNum,1) as int) end,buildingNum


如果太复杂了，就只好用函数了

[hnchddjq]

SELECT buildingNum
FROM Table
ORDER BY LEFT(buildingNum, 1), LEFT(buildingNum, 2),
LEFT(buildingNum, 3),LEFT(buildingNum, 4),LEFT(buildingNum, 5)


如果还有更长得编号，后面再多接几个LEFT(buildingNum, 6)之类的东东