Click here to Skip to main content
14,574,641 members

Sort AlphaNumeric Data in Sql

Rate this:
4.95 (13 votes)
Please Sign up or sign in to vote.
4.95 (13 votes)
23 Aug 2011CPOL
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 Scaler valued 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 Freelance Developer and Consultant, Architect, Blogger, Speaker, and Ex Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Node/Express, 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.
Linked in Profile:
He can be contacted at

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
Kiran Freelancer23-Aug-11 8:59
professionalKiran Freelancer23-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.

Posted 23 Aug 2011


12 bookmarked