Alphanumeric Sorting in SQL using PARSEAME

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 308
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 =PARSENAME(REPLACE(Code,'/','.'),2)
,RightPart=PARSENAME(REPLACE(Code,'/','.'),1)
,Rn=ROW_NUMBER() OVER(ORDER BY PARSENAME(REPLACE(Code,'/','.'),1))
From @t x)
Select
l.Id
,Code = Concat(l.LeftPart,'/',r.RightPart)
From (Select c.Id,c.LeftPart From Cte c) l
Join (Select c.Rn,c.RightPart From Cte c) r ON l.Id = r.Rn

Comments or Responses

Login to post response