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