Click here to Skip to main content
14,327,148 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.


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.


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:


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
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
     When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
     Else ID


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,
From Test


Any suggestions/comments are welcome!

Points of Interest

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


  • 7th January, 2010: Initial version.


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


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

Posted 11 Jan 2010


16 bookmarked