CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
Arunprasanth K V
Arunprasanth K V

Reputation: 21931

Hirarchical sorting in sql server when child id contains '.'

i have some childid like below 

childid
------------

1.1
1.2
2.8
2.7
6.5
6.5.1
6.5.15
7.1
8

sort order will be 

childid 
--------
1.1
1.2
2.7
2.8
6.5
6.51
6.5.15
7.1

i tried to converted to intiger like below 

declare @str nvarchar(max)='1.23.2';

set @str=(select replace(@str,'.',''))
select @str

but it fails when 

7.1
8

comes it gives order like 

8
7.1

but i need order like below

7.1
8

also if number like 

7.1.1
7.1.8
6.7.7.7

then order should be 

6.7.7.7
7.1.1
7.1.8

i hope somebody can help me to solve this

Upvotes: 4

Views: 72

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

EDIT: I changed the approach to deal with non-numeric values like '123abc' too.

declare @ids table(idList varchar(100))
insert into @ids values
 ('1.1')
,('1.2')
,('2.8')
,('2.7')
,('6.5')
,('6.5.1')
,('6.5.15')
,('7.1')
,('8');

select idList,padded.OrderBy
from @ids as ids
cross apply(select cast('<r>' + replace(idList,'.','</r><r>') + '</r>' as xml)) as AsXml(val)
cross apply
(
    select right('                ' + rtrim(x.y.value('.','varchar(max)')),10) 
    from AsXml.val.nodes('/r') as x(y)
    for xml path('')
) as padded(OrderBy)
order by padded.OrderBy

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Not an elegant solution but it works for me:

DECLARE @t TABLE ( childid VARCHAR(100) )
INSERT  INTO @t
VALUES  ( '1.1' ),
        ( '1.2' ),
        ( '2.8' ),
        ( '2.7' ),
        ( '6.5' ),
        ( '6.5.1' ),
        ( '6.5.15' ),
        ( '7.1' ),
        ( '8' )

;WITH cte AS(SELECT childid + '.' AS childid FROM @t)
SELECT LEFT(childid, LEN(childid) - 1) AS childid
FROM cte
CROSS APPLY(SELECT CHARINDEX('.', childid) i1) c1
CROSS APPLY(SELECT CASE WHEN i1 = 0 THEN 0 ELSE CHARINDEX('.', childid, i1 + 1) END i2) c2
CROSS APPLY(SELECT CASE WHEN i2 = 0 THEN 0 ELSE CHARINDEX('.', childid, i2 + 1) END i3) c3
CROSS APPLY(SELECT CASE WHEN i3 = 0 THEN 0 ELSE CHARINDEX('.', childid, i3 + 1) END i4) c4
CROSS APPLY(SELECT CASE WHEN i4 = 0 THEN 0 ELSE CHARINDEX('.', childid, i4 + 1) END i5) c5
ORDER BY
        CASE WHEN i1 = 0 THEN childid ELSE SUBSTRING(childid, 1, i1 - 1) END,
        CASE WHEN i2 = 0 THEN '0' ELSE SUBSTRING(childid, i1 + 1, i2 - i1 - 1) END,
        CASE WHEN i3 = 0 THEN '0' ELSE SUBSTRING(childid, i2 + 1, i3 - i2 - 1) END,
        CASE WHEN i4 = 0 THEN '0' ELSE SUBSTRING(childid, i3 + 1, i4 - i3 - 1) END,
        CASE WHEN i5 = 0 THEN '0' ELSE SUBSTRING(childid, i4 + 1, i5 - i4 - 1) END

Each cross apply is for getting next value between dots. Downside of this approach is that it is not dynamic and you should add as many cross applies as there could be nesting levels.

Upvotes: 1

Related Questions