mardi 22 septembre 2015

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

i have a parent child relation 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

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

Aucun commentaire:

Enregistrer un commentaire