CODEWITHSUNDEEP
sql-serversql-server-2005
Hadad
Hadad

Reputation: 344

Sorting nvarchar column SQL Server 2005

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

Upvotes: 3

Views: 2436

Answers (4)

Raj More
Raj More

Reputation: 48048

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)

Upvotes: 2

Curtis
Curtis

Reputation: 103428

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)

Upvotes: 3

JohnD
JohnD

Reputation: 14787

Your sort value should be converted to a numeric value.

ORDER BY CONVERT(INT, YourVarcharCol) ASC

Hope this helps,

John

Upvotes: 0

Talha Ahmed Khan
Talha Ahmed Khan

Reputation: 15473

you can type cast the values in number like int or float 

SELECT nVarCharValue FROM table ORDER BY CAST(nVarCharValue AS INT)

Upvotes: 0

Related Questions