Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Sort AlphaNumeric Data in Sql

4.95/5 (16 votes)
23 Aug 2011CPOL 24.6K  
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:

SQL
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:

SQL
select * from Department order by dbo.AlphaNum(DeptKey)

The result will be:

SQL
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)