2012-05-18

How to get MS SQL Server nvarchar() field to sort correctly using ORDER BY

I had an nvarchar field in MS SQL Server that wasn't sorting correctly when I did an ORDER BY on it.  For example, 10 would be put before 9 if I sorted ASC.  It turns out that MS SQL Server considers the position of each character in the field in sorting, and since the field is nvarchar (variable width) the digits of a number are not in consistent positions in the field.  I found the solution in this post on stackoverflow.com:

Order By Clause for NVARCHAR column in SQL Server

The specific code is:

ORDER BY RIGHT(REPLICATE(N' ', 50) + the_column_you_want_to_sort_by, 50)

What this does is virtually add enough leading whitespaces to each entry to make the column look like a fixed width column for the purposes of the query.