How do I order a list with entries like x.xy.z?

Transact-SQL
1

It's not the first time that I stumble over this problem to order a list with this kind of entries (1,3.10.3,3.1.10,...) With the new SQL functions (2016) there must be smart way to do it?

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp (structure Nvarchar(20))

insert into #Temp 
select '1' union all
select '3.10.3' union all
select '3.1.10' union all
select '10.0.1' union all
select '4.11' union all
select '4.1.0.1'

A good starting point seems to me this query, that splits the structure. The rest should be smart grouping... but I can't do it!

select a.*,row_number() over (partition by structure order by structure) as ord, value from #Temp a
cross apply string_split(structure,'.')
order by structure
2

How about doing a

Select dotty, cast(replace(dotty,".","") as int) order by 2

Will it alway be numbers?

3

It will always be numbers, but the result can't be the basis for an order procedure. 3.10.3 would become bigger than 1 and smaller than 2.1.1.1.1

5

You can't safely use STRING_SPLIT here, since it does not guarantee that values will be returned in the same order as they appeared in the original string.

Here's code that uses the DelimitedSplit8K function to provide the values for the sort:

SELECT *
FROM #Temp
CROSS APPLY (
    SELECT CAST(MAX(CASE WHEN ItemNumber = 1 THEN Item END) AS int) AS s1,
        CAST(MAX(CASE WHEN ItemNumber = 2 THEN Item END) AS int) AS s2,
        CAST(MAX(CASE WHEN ItemNumber = 3 THEN Item END) AS int) AS s3,
        CAST(MAX(CASE WHEN ItemNumber = 4 THEN Item END) AS int) AS s4
    FROM (
        SELECT * FROM dbo.DelimitedSplit8K(structure, '.')
    ) AS derived
) AS ca1
ORDER BY s1, s2, s3, s4
7

Maybe this?

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp (structure Nvarchar(20))


insert into #Temp 
select '1' union all
select '3.10.3' union all
select '3.1.10' union all
select '10.0.1' union all
select '4.11' union all
select '4.1.0.1' union all
select '2.1.1.1.1'

declare @max int ;

select @max = max(len(structure)) from #Temp

select REPLICATE('0', @max - LEN(structure)) + structure, * 
  From #Temp
  order by 1

drop table #Temp
9

hi barnabeck ...

from . Itzik ben Gan ( MVP ... idea ... highly senior talented author of many books articles videos )
see hierarchyid ... trick makes it very simple and easy

drop create data ... 
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp (structure Nvarchar(20))

insert into #Temp 
select '1' union all
select '3.10.3' union all
select '3.1.10' union all
select '10.0.1' union all
select '4.11' union all
select '4.1.0.1'

select * from #temp go
SQL .... 
SELECT structure 
FROM #temp 
ORDER BY  Cast ('/' + structure + '/' AS  HIERARCHYID)

image

1 Like
10

But make sure you never have leading zeros in the values: those aren't allowed in hierarchies. That is, this would be invalid:

1.3.02.1

11

Totally happy with the solution harishgg1 came up with. Thank you!
... and to Scott: good to know about the leading zeros, but there is no risk that they show up!
yosiasz... this does not wor either as you can prove by yourself. Thank you anyway!