Alphanumeric Sorting in SQL

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 360
Let's say we have a table as

Declare @t Table( Id int, Code varchar(20) )

insert into @t values(1,'AAAA/00001')
insert into @t values(2,'BBB/00010')
insert into @t values(3,'CCC/00002')
insert into @t values(4,'ZZZ/00100')


We need to sort the Code value such that the output will be as

Id	Code
1 AAAA/00001
2 BBB/00002
3 CCC/00010
4 ZZZ/00100


The below code will do so

Declare @t Table( Id int, Code varchar(20) )

insert into @t values(1,'AAAA/00001')
insert into @t values(2,'BBB/00010')
insert into @t values(3,'CCC/00002')
insert into @t values(4,'ZZZ/00100')

;With Cte AS(
Select
x.Id
,LeftPart =LEFT(Code,PATINDEX('%/%',Code)-1)
,RightPart=SUBSTRING(Code,CHARINDEX('/',Code)+1,LEN(Code))
,Rn=ROW_NUMBER() OVER(ORDER BY SUBSTRING(Code,CHARINDEX('/',Code)+1,LEN(Code)))
From @t x)
,LeftCte AS(
Select
c.Id
,c.LeftPart
From Cte c
)
,RightCte AS(

Select
c.Rn
,c.RightPart

From Cte c

)
Select
l.Id
,Code = Concat(l.LeftPart,'/',r.RightPart)
From LeftCte l
Join RightCte r ON l.Id = r.Rn

Comments or Responses

Login to post response