SQL Server: sort a column numerically if possible, otherwise alpha

Question

I am working with a table that comes from an external source, and cannot be "cleaned". There is a column which an nvarchar(20) and contains an integer about 95% of the time, but occasionally contains an alpha. I want to use something like

select * from sch.tbl order by cast(shouldBeANumber as integer)

but this throws an error on the odd "3A" or "D" or "SUPERCEDED" value.

Is there a way to say "sort it like a number if you can, otherwise just sort by string"? I know there is some sloppiness in that statement, but that is basically what I want.

Lets say for example the values were

7,1,5A,SUPERCEDED,2,5,SECTION

I would be happy if these were sorted in any of the following ways (because I really only need to work with the numeric ones)

1,2,5,7,5A,SECTION,SUPERCEDED
1,2,5,5A,7,SECTION,SUPERCEDED
SECTION,SUPERCEDED,1,2,5,5A,7
5A,SECTION,SUPERCEDED,1,2,5,7

Answer 1

select 
  * 
from 
  sch.tbl 
order by 
  case isnumeric(shouldBeANumber) 
  when 1 then cast(shouldBeANumber as integer)
  else 0
  end

Answer 2

SELECT
  (CASE ISNUMERIC(shouldBeANumber)
    WHEN 1 THEN
      RIGHT(CONCAT('00000000',shouldBeANumber), 8)
    ELSE
      shouoldBeANumber) AS stringSortSafeAlpha
ORDEER BY
  stringSortSafeAlpha

This will add leading zeros to all shouldBeANumber values that truly are numbers and leave all remaining values alone. This way, when you sort, you can use an alpha sort but still get the correct values (with an alpha sort, "100" would be less than "50", but if you change "50" to "050", it works fine). Note, for this example, I added 8 leading zeros, but you only need enough leading zeros to cover the largest possible integer in your column.

Answer 3

Provided that your numbers are not more than 100 characters long:

WITH    chars AS
        (
        SELECT  1 AS c
        UNION ALL
        SELECT  c + 1
        FROM    chars
        WHERE   c <= 99
        ),
        rows AS
        (
        SELECT  '1,2,5,7,5A,SECTION,SUPERCEDED' AS mynum
        UNION ALL
        SELECT  '1,2,5,5A,7,SECTION,SUPERCEDED'
        UNION ALL
        SELECT  'SECTION,SUPERCEDED,1,2,5,5A,7'
        UNION ALL
        SELECT  '5A,SECTION,SUPERCEDED,1,2,5,7'
        )
SELECT  rows.*
FROM    rows
ORDER BY
        (
        SELECT  SUBSTRING(mynum, c, 1) AS [text()]
        FROM    chars
        WHERE   SUBSTRING(mynum, c, 1) BETWEEN '0' AND '9'
        FOR XML PATH('')
        ) DESC

Answer 4

I really only need to work with the numeric ones

this will give you only the numeric ones, sorted properly:

SELECT
    *
    FROM YourTable
    WHERE ISNUMERIC(YourColumn)=1
    ORDER BY YourColumn