Version number sorting in Sql Server

Question

I have a table that stores the versions as under

Declare @tblVersion table(VersionNumber varchar(100))
 Insert into @tblVersion Values('1.3.1')
 Insert into @tblVersion Values('1.3.2.5')
 Insert into @tblVersion Values('1.4.1.7.12')
 Insert into @tblVersion Values('1.4.11.14.7')
 Insert into @tblVersion Values('1.4.3.109.1')
 Insert into @tblVersion Values('1.4.8.66')

 Select * From @tblVersion

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.11.14.7
1.4.3.109.1
1.4.8.66

My requirement is that I need to sort them so that the output will be

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.3.109.1
1.4.8.66
1.4.11.14.7

But if do a simple order by it does not work as expected

Select VersionNumber
 From @tblVersion
 Order By VersionNumber

VersionNumber
1.3.1
1.3.2.5
1.4.1.7.12
1.4.11.14.7
1.4.3.109.1
1.4.8.66

Help needed

This is a special case of the general "don't store delimited values in columns" problem — Joel Coehoorn, Nov 14 '11 at 17:10

score 9 · Answer 1 · edited Oct 24 '11 at 18:36

9

If you are using SQL Server 2008 or later, you can leverage the hierarchyID data type:

select * from @tblVersion
order by CAST('/'+REPLACE(VersionNumber,'.','/')+'/' as hierarchyID)

edited Oct 24 '11 at 18:36

Bill the Lizard

398,270
210
566
880

answered Oct 24 '11 at 04:41

Steve Kass

7,144
20
26

2

I've only just realised: you don't need to replace the `.` with `/`, `ORDER BY CAST('/'+VersionNumber+'/' as hierarchyID)` works just as well. – Andriy M Sep 20 '15 at 14:24

score 4 · Answer 2 · answered Oct 24 '11 at 03:20

This is generally called natural sort and there is no easy way to do it in SQL Server. Generally the data needs to be broken into fields or to fixed length segments of a field. It can be sorted on those field(s) for the desired order.

VersionNumber VersionSort
1.3.1         0001.0003.0001
1.3.2.5       0001.0003.0002.0005
1.4.1.7.12    0001.0004.0001.0007.0012
1.4.11.14.7   0001.0004.0011.0014.0007
1.4.3.109.1   0001.0004.0003.0109.0001
1.4.8.66      0001.0004.0008.0066

This is not an answer – HamsterWithPitchfork Oct 28 '22 at 12:08 — HamsterWithPitchfork, Oct 28 '22 at 12:08

score 1 · Answer 3 · edited May 23 '17 at 11:45

If you are on SQL Server 2005 or later and the number of possible fields in version numbers is fixed, you could try the following approach:

SELECT t.*
FROM @tblVersion t
  CROSS APPLY (SELECT v = NULLIF(CHARINDEX('.', '.' + t.VersionNumber,        1), 0)) v1
  CROSS APPLY (SELECT v = NULLIF(CHARINDEX('.', '.' + t.VersionNumber, v1.v + 1), 0)) v2
  CROSS APPLY (SELECT v = NULLIF(CHARINDEX('.', '.' + t.VersionNumber, v2.v + 1), 0)) v3
  CROSS APPLY (SELECT v = NULLIF(CHARINDEX('.', '.' + t.VersionNumber, v3.v + 1), 0)) v4
  CROSS APPLY (SELECT v = NULLIF(CHARINDEX('.', '.' + t.VersionNumber, v4.v + 1), 0)) v5
ORDER BY
  CAST(SUBSTRING(t.VersionNumber, v1.v, v2.v - v1.v - 1) AS int),
  CAST(SUBSTRING(t.VersionNumber, v2.v, v3.v - v2.v - 1) AS int),
  CAST(SUBSTRING(t.VersionNumber, v3.v, v4.v - v3.v - 1) AS int),
  CAST(SUBSTRING(t.VersionNumber, v4.v, v5.v - v4.v - 1) AS int),
  CAST(SUBSTRING(t.VersionNumber, v5.v, 999) AS int)

All fields of a version number are extracted one by one and converted to integers, which are then used for sorting. (Basically, @Brian's idea, as it turns out.)

score 1 · Accepted Answer · answered Oct 24 '11 at 11:22

Implementation of Brain's Solution

Declare @tblVersion table(VersionNumber varchar(100)) 
 Insert into @tblVersion Values('1.3.1') 
 Insert into @tblVersion Values('1.3.2.5') 
 Insert into @tblVersion Values('1.4.1.7.12') 
 Insert into @tblVersion Values('1.4.11.14.7') 
 Insert into @tblVersion Values('1.4.3.109.1') 
 Insert into @tblVersion Values('1.4.8.66') 

 --Select * From @tblVersion 

 ;With CTE AS
 (
    Select 
        Rn = Row_Number() Over(Order By (Select 1))
        ,VersionNumber
    From @tblVersion
)
,CTESplit AS
(
    SELECT 
            F1.Rn,
            F1.VersionNumber,
            VersionSort = 
                            Case 
                                    When Len(O.VersionSort) = 1 Then '000' + O.VersionSort
                                    When Len(O.VersionSort) = 2 Then '00' + O.VersionSort
                                    When Len(O.VersionSort) = 3 Then '0' + O.VersionSort
                                    When Len(O.VersionSort) = 4 Then O.VersionSort
                            End

    FROM
    (
        SELECT *,
        cast('<X>'+replace(F.VersionNumber,'.','</X><X>')+'</X>' as XML) as xmlfilter from CTE F
    )F1
 CROSS APPLY
 ( 
    SELECT fdata.D.value('.','varchar(50)') as VersionSort  
    FROM f1.xmlfilter.nodes('X') as fdata(D)) O
 )
 ,CTE3 As(
Select 
        --Rn 
        --,
        VersionNumber
        ,SortableVersion = 
                            Stuff(
                                    (Select '.' + Cast(VersionSort As Varchar(100))
                             From CTESplit c2
                             Where c2.Rn = c1.Rn
                             For Xml Path('')),1,1,'')
From CTESplit c1
Group By c1.Rn,c1.VersionNumber
)
Select VersionNumber
From CTE3
Order By SortableVersion

score 0 · Answer 5 · answered Oct 24 '11 at 03:20

I cannot see the solution using standard sorting etc, but I think you need the UDF that adds the "0" symbols before each symbol, like

001.003.001
001.003.002.005
001.004.001.007.012
001.004.011.014.007
001.004.003.109.001
001.004.008.066

and then sort by these changed values

score 0 · Answer 6 · answered Oct 24 '11 at 03:24

0

The SELECT query is ordering alphabetically, rather than numerically. Without a stored procedure to add leading zeros into the separate components of the version number to make them all the same length, you cannot (AFAIK) do this in SQL.

answered Oct 24 '11 at 03:24

Chris Walton

2,513
3
25
39

score 0 · Answer 7 · answered Nov 14 '11 at 17:08

0

Check these helps you

Select * From @tblVersion order by replace(VersionNumber,'.','')

answered Nov 14 '11 at 17:08

Mahesh Patil

11

Version number sorting in Sql Server

7 Answers7

Linked