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