Click here to Skip to main content
14,268,184 members

How to Sort Alphanumeric Data in SQL

Rate this:
3.27 (3 votes)
Please Sign up or sign in to vote.
3.27 (3 votes)
12 Jan 2010CPOL
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:

image002.jpg

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

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

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)

Share

About the Author

Ravi Kallamadi
United States United States
No Biography provided

Comments and Discussions

 
Questionwanted the same answer but for sqlite Pin
Member 1358603519-Dec-17 21:04
memberMember 1358603519-Dec-17 21:04 
QuestionAlpahanumeric sort Pin
emke19-Jan-16 2:30
memberemke19-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
membermohsinmushtaq27-Apr-13 21:35 
GeneralMy vote of 4 Pin
Ssafrin25-Oct-11 20:47
memberSsafrin25-Oct-11 20:47 
QuestionHow to to do this while using LINQ Pin
Vishant Patil3-Aug-11 4:43
memberVishant Patil3-Aug-11 4:43 
Generalsorting alpha numeric data Pin
Srabanjit guha31-Jan-10 3:10
memberSrabanjit guha31-Jan-10 3:10 
Generalthis works for me.. Pin
k_cire042619-Jan-10 15:04
memberk_cire042619-Jan-10 15:04 
this is a great sorting sql code. however, i'm still figuring this out on how this sql code sort it...detailed explanation would be appreciated..
GeneralRe: this works for me.. Pin
Ravi Kallamadi20-Jan-10 10:53
memberRavi Kallamadi20-Jan-10 10:53 
GeneralNot Really Working Pin
Ashfield18-Jan-10 2:45
memberAshfield18-Jan-10 2:45 
GeneralRe: Not Really Working Pin
Ravi Kallamadi18-Jan-10 16:35
memberRavi Kallamadi18-Jan-10 16:35 
GeneralThanks. Works great Pin
SubzeroDragon11-Jan-10 4:49
memberSubzeroDragon11-Jan-10 4:49 
GeneralRe: Thanks. Works great Pin
Ravi Kallamadi13-Jan-10 5:08
memberRavi Kallamadi13-Jan-10 5:08 
GeneralRe: Thanks. Works great Pin
SubzeroDragon14-Jan-10 4:08
memberSubzeroDragon14-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.

Article
Posted 11 Jan 2010

Stats

70.5K views
16 bookmarked