Order By Clause for NVARCHAR column in SQL Server

Question

I need to make the order by for the nvarchar column. The issue is this that the column doesn't have the same pattern of entry, example of some rows of the column are

How to sort this column by order By clause? The only relief I have is that this column contains only numeric and alpha numeric characters.

What is the issue you are facing? Is `order by ` not working? — Sachin Shanbhag, Dec 09 '10 at 11:19
@Sachin No it is not working correctly. By doing that it feels like that it is not at all sorting the records — Zerotoinfinity, Dec 09 '10 at 11:28
@Zerotoinfinite - Can you show some sample results you are getting and the result which you are expecting? That would help. — Sachin Shanbhag, Dec 09 '10 at 11:29
I was adviced by my client to sort it. He is expecting output like first numeric value than alphanumeric. In this case. 9 , 12,78F, T2R, TEF — Zerotoinfinity, Dec 09 '10 at 11:34
There are many different possible output orders that *might* be desired, other than the string sorting (what you'd get by just saying `order by column`). For instance, you might want all purely numeric values to sort first, and sorted numerically (so 2 comes between 1 and 10), or more complex rules (e.g. all values *starting* numerically sorted by numeric rules, then by string rules for the remained). You need to tell us what rules you think "make sense" in your case. — Damien_The_Unbeliever, Dec 09 '10 at 11:36

score 5 · Accepted Answer · answered Dec 09 '10 at 11:39

5

ORDER BY
   RIGHT(REPLICATE(N' ', 500) + MyValue, 500)

So

9
12
78F
T2R
TEF

becomes

Which will sort correctly

You can't rely on ISNUMERIC

answered Dec 09 '10 at 11:39

gbn

422,506
82
585
676

Can you please explain the logic you used in this statement. I am new in sql server – Zerotoinfinity Dec 09 '10 at 12:20
Simply, it add leading spaces which are included in the sort – gbn Dec 09 '10 at 12:28
And yet 'A' would sort between '9' and '12' here... Still, if the OP's client is happy, great. – Andriy M Jan 06 '11 at 13:15
@Andriy M: This solution kind of assumes there is some length element too to the sort too: so 9 – gbn Jan 06 '11 at 13:48

score 2 · Answer 2 · answered Dec 09 '10 at 11:39

2

select
    *
from
    table
order by
    CASE
       WHEN not column like '%[^0-9]%' THEN CONVERT(int,column)
       WHEN column like '[0-9]%' THEN CONVERT(int,SUBSTRING(column,1,PATINDEX('%[A-Z]%',column)-1))
    END,
    CASE
       WHEN not column like '%[^0-9]%' THEN NULL
       WHEN column like '[0-9]%' THEN SUBSTRING(column,PATINDEX('%[A-Z]%',column),9000)
       ELSE column
    END

answered Dec 09 '10 at 11:39

Damien_The_Unbeliever

234,701
27
340
448

+1 for most sensible answer but even then, what do you do with decimals, scientific notation etc? The client would do well to define the desired behaviour – Tao Jun 07 '11 at 13:29

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

I'm guessing that you issue is that its not sorting the numbers and text correctly.

This article explains one approach:

How do I sort a VARCHAR column in SQL server that contains numbers?

From the article:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate(Char(0), 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

edited May 23 '17 at 12:26

Community

1
1

answered Dec 09 '10 at 11:34

James Wiseman

29,946
17
95
158

2

IsNumeric is pure evil, unless you happen to like that `'1d2'` is numeric, but `'1g2'` isn't. – Damien_The_Unbeliever Dec 09 '10 at 11:41
ugh! Mental note to self. +1 to your question for pointing that out – James Wiseman Dec 09 '10 at 13:10
@Damien_The_Unbeliever: This is very interesting - do you know of anywhere this behaviour is explained or at least documented? – Tao Jun 07 '11 at 13:26

Order By Clause for NVARCHAR column in SQL Server

3 Answers3