Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

How to Sort Alphanumeric Data in SQL

Rate me:
Please Sign up or sign in to vote.
3.42/5 (4 votes)
12 Jan 2010CPOL1 min read 88K   16   14
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:

Image 1

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

image002.jpg

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

SQL
Select ID From TestOrder by ID

image003.jpg

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
SQL
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

image004.jpg

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

SQL
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

image005.jpg

Any suggestions/comments are welcome!

Points of Interest

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

History

  • 7th January, 2010: Initial version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionwanted the same answer but for sqlite Pin
Member 1358603519-Dec-17 21:04
Member 1358603519-Dec-17 21:04 
QuestionAlpahanumeric sort Pin
emke19-Jan-16 2:30
emke19-Jan-16 2:30 
SuggestionUse this one Pin
Saddamhusen Uadanwala16-Dec-14 18:39
professionalSaddamhusen Uadanwala16-Dec-14 18:39 
QuestionPerfect Pin
mohsinmushtaq27-Apr-13 21:35
mohsinmushtaq27-Apr-13 21:35 
GeneralMy vote of 4 Pin
Ssafrin25-Oct-11 20:47
Ssafrin25-Oct-11 20:47 
QuestionHow to to do this while using LINQ Pin
Vishant Patil3-Aug-11 4:43
Vishant Patil3-Aug-11 4:43 
Generalsorting alpha numeric data Pin
Srabanjit guha31-Jan-10 3:10
Srabanjit guha31-Jan-10 3:10 
Generalthis works for me.. Pin
k_cire042619-Jan-10 15:04
k_cire042619-Jan-10 15:04 
GeneralRe: this works for me.. Pin
Ravi Kallamadi20-Jan-10 10:53
Ravi Kallamadi20-Jan-10 10:53 
GeneralNot Really Working Pin
Ashfield18-Jan-10 2:45
Ashfield18-Jan-10 2:45 
GeneralRe: Not Really Working Pin
Ravi Kallamadi18-Jan-10 16:35
Ravi Kallamadi18-Jan-10 16:35 
GeneralThanks. Works great Pin
SubzeroDragon11-Jan-10 4:49
SubzeroDragon11-Jan-10 4:49 
GeneralRe: Thanks. Works great Pin
Ravi Kallamadi13-Jan-10 5:08
Ravi Kallamadi13-Jan-10 5:08 
Thanks! I am sorry, I do not have MySQL version. I think this concept should work for any database.
GeneralRe: Thanks. Works great Pin
SubzeroDragon14-Jan-10 4:08
SubzeroDragon14-Jan-10 4:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.