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

求救高手，这句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 codeselect 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