How to Sort Alphanumeric Data in SQL

Ravi Kallamadi

3.42/5 (4 votes)

Jan 11, 2010

CPOL

1 min read

90169

Sort alphanumeric data 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.

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

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.)

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