Sorting nvarchar column as integer

Question

I have mixed data i nvarchar column (words and numbers). Which is fastest way to sort data in this column in Numeric Order.

Result example:

• 1
• 2
• 3
• ...
• 10
• 11
• ...
• aaaa
• aaab
• b
• ba
• ba
• ...

Answer 1

Use this:

ORDER BY
    CASE WHEN ISNUMERIC(column) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(column) = 1 THEN CAST(column AS INT) ELSE 0 END,
    column

This works as expected.

---

Note: You say fastest way. This sql was fast for me to produce, but the execution plan shows a table-scan, followed by a scalar computation. This could possibly produce a temporary result containing all the values of that column with some extra temporary columns for the ISNUMERIC results. It might not be fast to execute.

Answer 2

If you left pad your numbers with 0's and sort on that, you will get your desired results. You'll need to make sure that the number of 0's you pad with matches the size of the varchar column.

Take a look at this example...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('1')
Insert Into @Temp Values('2')
Insert Into @Temp Values('3')
Insert Into @Temp Values('10')
Insert Into @Temp Values('11')
Insert Into @Temp Values('aaaa')
Insert Into @Temp Values('aaab')
Insert Into @Temp Values('b')
Insert Into @Temp Values('ba')
Insert Into @Temp Values('ba')

Select * From @Temp
Order By Case When IsNumeric(Data) = 1 
              Then Right('0000000000000000000' + Data, 20) 
              Else Data End

Also note that it is important when using a case statement that each branch of the case statement returns the same data type, or else you will get incorrect results or an error.