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
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
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
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)
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!