Click here to Skip to main content
Click here to Skip to main content

How to Sort Alphanumeric Data in SQL

, 12 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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

 
QuestionPerfect Pinmembermohsinmushtaq27-Apr-13 21:35 
this is just what i needed ! and as a new programmer just by this article i learned alot
 
Thanks Thumbs Up | :thumbsup:
GeneralMy vote of 4 PinmemberSsafrin25-Oct-11 20:47 
QuestionHow to to do this while using LINQ PinmemberVishant Patil3-Aug-11 4:43 
Generalsorting alpha numeric data PinmemberSrabanjit guha31-Jan-10 3:10 
Generalthis works for me.. Pinmemberk_cire042619-Jan-10 15:04 
GeneralRe: this works for me.. PinmemberRavi Kallamadi20-Jan-10 10:53 
GeneralNot Really Working PinmemberAshfield18-Jan-10 2:45 
GeneralRe: Not Really Working PinmemberRavi Kallamadi18-Jan-10 16:35 
GeneralThanks. Works great PinmemberSubzeroDragon11-Jan-10 4:49 
GeneralRe: Thanks. Works great PinmemberRavi Kallamadi13-Jan-10 5:08 
GeneralRe: Thanks. Works great PinmemberSubzeroDragon14-Jan-10 4:08 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140826.1 | Last Updated 12 Jan 2010
Article Copyright 2010 by Ravi Kallamadi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid