how do you order numbers such as 1.01,1.01.2 in sql server-CodePudding

I have a column that a user can enter numbers like 1.01. so my table in sql ends up looking like

Id
1.01
1.03.04
2.1
1.01.01
1.
2.
1.02

how can i order this column so that it looks like

1.
1.01
1.01.01
1.02
1.03.04
2.
2.1

If it was 2 decimal places i would have cast it as a decimal, but this doesnt work for this instance since i have numbers like 1.01.01

found reference SQL, varchar field with decimals entries = ORDER BY not sorting proprely

CodePudding user response：

As Larnu suggested, hierarchyid ... but this took a little cleanup like leading zeros and the double/trailing //

Declare @YourTable Table ([Id] varchar(50))  Insert Into @YourTable Values 
 ('1.01')
,('1.03.04')
,('2.1')
,('1.01.01')
,('1.')
,('2.')
,('1.02')
 
Select * 
 from  @YourTable
 Order by try_convert(hierarchyid,replace('/' replace(replace(ID,'.0','.'),'.','/') '/','//','/'))

Results

Id
1.
1.01
1.01.01
1.02
1.03.04
2.
2.1