CODEWITHSUNDEEP
sql-serversql-server-2005sql-order-bynvarchar
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Order By Clause for NVARCHAR column in SQL Server

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

12    
9     
TEF      
78F    
T2R

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.

Upvotes: 0

Views: 5949

Answers (3)

Mutation Person
Mutation Person

Reputation: 30500

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

Upvotes: 0

gbn
gbn

Reputation: 432311

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

So

9
12
78F
T2R
TEF

becomes

            9
           12
          78F
          T2R
          TEF

Which will sort correctly

You can't rely on ISNUMERIC

Upvotes: 5

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

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

Upvotes: 2

Related Questions