Sorting nvarchar column SQL Server 2005

Question

When sorting a nvarchar column in SQL that has a numeric value, SQL Server returns bad sorting and I don't know why!

Is there any particular way SQL Server sorts varchar columns having numeric values?

Thanks

Answer 1

This has a fail-safe which will take into account non numerical values, and place them at the end of your result set.

SELECT [nvarcharColumn]
FROM [Table]
ORDER BY CAST(ISNULL(NULLIF(ISNUMERIC([nvarcharColumn]),0),2147483647) as INT)

Answer 2

If you want it sorted as numbers, you will have to convert it to numbers.

Check here is a list of numbers in an nVarChar column being sorted:

With MyTable AS
(
    Select Cast ('1' as nVarChar) nVarCharColumn UNION 
    Select '100' UNION
    Select '99'
)
Select *
From MyTable
Order by 1

Logically 99 should come before the 100, right? But it is sorted on nVarChar at a time, so the first 1 of the 100 is smaller than the first 9 and therefore it comes before 99.

nVarCharColumn
------------------------------
1
100
99

(3 row(s) affected)

If you CAST then you get the desired output

With MyTable AS
(
    Select Cast ('1' as nVarChar) nVarCharColumn UNION 
    Select '100' UNION
    Select '99'
)
Select *
From MyTable
Order by CAST (nVarCharColumn as Int)

Here is the proper output

nVarCharColumn
------------------------------
1
99
100

(3 row(s) affected)

Here's a twist in the table. What if there is a string value in the column as well?

;With MyTable AS
(
    Select Cast ('1' as nVarChar) nVarCharColumn UNION 
    Select '100' UNION
    Select '99' UNION
    Select 'String Value'
)
Select *
From MyTable
Order by CAST (nVarCharColumn as Int)

Error!!

Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the nvarchar value 'String Value' to data type int.

So then the SORT part checks IsNumeric - no need to do the same thing in the SELECT part.

With MyTable AS
(
    Select Cast ('1' as nVarChar) nVarCharColumn UNION 
    Select '100' UNION
    Select '99' UNION
    Select 'String Value'
)
Select *
From MyTable
Order by CAST ( Case When ISNUMERIC (nVarCharColumn)=1 Then nVarCharColumn Else -99 End as Int)

Get the proper output

nVarCharColumn
------------------------------
String Value
1
99
100

(4 row(s) affected)