# Alphanumeric Sorting in SQL using PARSEAME

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 530
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	Code1	AAAA/000012	BBB/000023	CCC/000104	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) lJoin (Select c.Rn,c.RightPart From Cte c) r ON l.Id = r.Rn`