Click here to Skip to main content
13,453,725 members (42,543 online)
Click here to Skip to main content
Add your own
alternative version


12 bookmarked
Posted 23 Aug 2011

Sort AlphaNumeric Data in Sql

, 23 Aug 2011
Rate this:
Please Sign up or sign in to vote.
I have gone through a lot of examples to sort alpha numeric data in sql. Here is my way:
The simple logic i used here is, extracted the number part from varchar and replaced it with 20 - length of that number (considering the no. of digits will not be more than 20 digits in varchar. Else increse the value 20).
First of all, create a Function as following:

ALTER FUNCTION [dbo].[AlphaNum]
	@input varchar(max)
RETURNS varchar(max)
	declare @num varchar(50)
	declare @space varchar(50)
	declare @index int = 1
	set @num = LEFT(SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000) + 'X')-1)
	set @space = replicate(' ', 20 - len(@num))
	return replace(@input, @num, @space + @num)

How to Use:
select * from Department order by dbo.AlphaNum(DeptKey)

The result will be:
DepartmentId Department             DeptKey
------------ ---------------------  ------------------------
5            Main Admin             Admin1
3            Administrator          Admin2
1            Admin Permanent        Admin23
2            Admin on Contract      Admin45
4            Top Management         Admin100
7            Sales Team             Sales78
6            Trainer                Sales456

(7 row(s) affected)

Here, the result is alphanumerically sorted by DeptKey.


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


About the Author

Anurag Gandhi
India India
Anurag Gandhi is a Developer, Consultant, Architect, Blogger, and a Speaker. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Microsoft Azure/Cloud, web application hosting/architecture, Angular, AngularJs, design and development. His languages of choice are C#, Node/Express, JavaScript, Asp .NET MVC, Asp, C, C++. He is familiar with many other programming languages as well. He mostly works with MS Sql Server as the preferred database and has worked with Redis, MySQL, Oracle, MS Access, etc. also.
He is active in programming communities and loves to share the knowledge with others whenever he gets the time for it.
He is also a passionate chess player.
He can be contacted at:

You may also be interested in...


Comments and Discussions

QuestionThank You Pin
chandrashekhar sharma7-Jan-15 21:58
memberchandrashekhar sharma7-Jan-15 21:58 
GeneralReason for my vote of 5 Excellent Tip. This is very helpful... Pin
AnnuKiran23-Aug-11 8:59
memberAnnuKiran23-Aug-11 8:59 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180318.3 | Last Updated 23 Aug 2011
Article Copyright 2011 by Anurag Gandhi
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid