UmaShankar Patel (9/1/2012)
You need to select query as
select * from @NumberTableorder by Convert(numeric,REPLACE(REPLACE(ItemNumber,'ABC-',''),'-xyz','')) asc
The problem I see with this is that 'ABC' may not always be 'ABC', and 'XYZ' may not always be 'XYZ'. This should do it, albeit maybe a little bulky.
SELECT
ItemNumber
FROM @NumberTable
ORDER BY CAST(SUBSTRING(SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1) + 1,LEN(ItemNumber)),1,CHARINDEX('-',SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1)+1,LEN(ItemNumber)),1)-1) AS INT)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.