Click here to Skip to main content
13,799,964 members
Click here to Skip to main content
Add your own
alternative version

Stats

19.4K views
12 bookmarked
Posted 23 Aug 2011
Licenced CPOL

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)
AS
BEGIN
	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)
END


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.

License

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

Share

About the Author

Anurag Gandhi
Architect
India India
Anurag Gandhi is a Developer, Consultant, Architect, Blogger, Speaker and a Microsoft Employee. 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.
Linked in Profile: https://in.linkedin.com/in/anuraggandhi
He can be contacted at: soft.gandhi@gmail.com

Disclaimer: Any posts, article, content present in this website is my personal opinion based on best of my knowledge and experience. None of the content provided by me should be considered as official communication/opinion or my Employer.

You may also be interested in...

Comments and Discussions

 
QuestionThank You Pin
chandrashekhar sharma7-Jan-15 22:58
memberchandrashekhar sharma7-Jan-15 22:58 
GeneralReason for my vote of 5 Excellent Tip. This is very helpful... Pin
Kiran Freelancer23-Aug-11 9:59
professionalKiran Freelancer23-Aug-11 9: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 | Cookies | Terms of Use | Mobile
Web06 | 2.8.181214.1 | Last Updated 23 Aug 2011
Article Copyright 2011 by Anurag Gandhi
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid