求救高手，这句SQL的排序的有关问题

求救高手，这句SQL的排序的问题
select a from A where a not in (select a from B ) order by cast (substring(a,2,len(a)-3)) as int

A,B是表
a是字段
格式是W23_D

第一位是字母，然后是序号，最后加"-"和加一个字母

我想实现按序号排序的功能，请问该怎么写啊

------解决方案--------------------

SQL code

select a from A where a not in (select a from B )
order by 
case when len(a) < 3 then 0
     when charindex('-',a) < 3 then 0
     when patindex('%^[0-9]%',substing(a,2,charindex('-',a)-2))>0 then 0
     else substing(a,2,charindex('-',a)-2) end

------解决方案--------------------

SQL code

select a from A where a not in (select a from B )
order by 
case when len(a) < 3 then 0
     when charindex('-',a) < 3 then 0
     when patindex('%^[0-9]%',substing(a,2,charindex('-',a)-2))>0 then 0
     else cacst(substing(a,2,charindex('-',a)-2) as int) end

求救高手，这句SQL的排序的有关问题

相关推荐