SQL Server - How to sort alfanumeric value in SQL

Introduction

If your SQL query is not returning the result-set in the order you are expecting, this article may be helpful to fix the issue.

Background

We all know that the ORDER BY keyword is used to sort a result-set by a specified column. It works great for most of the cases. But, for alphanumeric data, it may not return the result-set that you will be expecting. This article explains how this can be fixed easily.

Using the Code

Step 1

I have created a table named “Test” with two columns, as shown below:

The following data has been added to the “Test” table:

The “Order By” in the following SQL query may not return the result-set in the correct order.

 Collapse | http://www.codeproject.com/KB/database/AlphanumericSort_SQL.aspx#

Select ID From TestOrder by ID

Step 2

I have modified the ORDER BY clause as shown below, and it returned the results in the proper order.

(Note: The ID column is defined as varchar(20). So, I did the following to fix this issue:

• If ID is numeric, add 21 '0's in front of the ID value and get the last 20 characters
• If ID is not numeric, add 21 ‘’s at the end of the ID value and get the first 20 characters

 Collapse | http://www.codeproject.com/KB/database/AlphanumericSort_SQL.aspx#

Select ID 
From Test
ORDER BY
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
     When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
     Else ID
End

Step 3

I have changed the query to return the row numbers (used in pagination) and it worked!

(Note: ROW_NUMBER works only in SQL Server 2005 and above versions.)

 Collapse | http://www.codeproject.com/KB/database/AlphanumericSort_SQL.aspx#

Select Row_Number() Over (Order by
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
                          When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
                        Else ID
               END) As RowNumber,
ID
From Test

Any suggestions/comments are welcome!

Points of Interest

There may be better ways of doing this. Please share your thoughts.

here u may get some idea to sort alfanumeric valus in sql

http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/0735fc88-d5f6-4171-8c10-7a410fe154e1/

http://www.mpopp.net/2006/06/sorting-of-numeric-values-mixed-with-alphanumeric-values/

hope this will help u

Hi All,
Today I am representing alphanumeric sorting using sql query. Sometimes we want to sort data like numeric first and after that all alphanumeric data but sql order by clause doesn’t support this functionality. So I have used replicate function for alphanumeric sorting.

Syntax of Replicate Function:
REPLICATE(character_expression, integer_expression)

character_expression 
An alphanumeric expression of character data, or other data types that are implicitly convertible to nvarchar or ntext.
integer_expression 
An expression that can be implicitly converted to int. If integer_expression is negative, a null string is returned.

Now comes to the main point how can we use this function in sql for alphanumeric sorting.

See below script

Here, I have defined alphanumeric column as NVARCHAR(50) so I have fixed it as following ways
1.    In case of numeric data, I have added 51 zeros in front of the column data and retrieve the last 50 character.
2.    In case of non numeric data, I have added 51 blank spaces at the end of the column data and retrieve the first 50 character.