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)
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.
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: https://in.linkedin.com/in/anuraggandhi
He can be contacted at soft.gandhi@gmail.com