# Alphanumeric Sorting in SQL

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 389
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 =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 lJoin RightCte r ON l.Id = r.Rn`