Click here to Skip to main content
12,447,625 members (58,502 online)
Click here to Skip to main content
Add your own
alternative version

Stats

50.4K views
16 bookmarked
Posted

How to Sort Alphanumeric Data in SQL

, 12 Jan 2010 CPOL
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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionAlpahanumeric sort Pin
emke19-Jan-16 2:30
memberemke19-Jan-16 2:30 
SuggestionUse this one Pin
Saddamhusen Uadanwala16-Dec-14 18:39
memberSaddamhusen Uadanwala16-Dec-14 18:39 
QuestionPerfect Pin
mohsinmushtaq27-Apr-13 21:35
membermohsinmushtaq27-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 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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160811.3 | Last Updated 12 Jan 2010
Article Copyright 2010 by Ravi Kallamadi
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid