Geek Answers Handbook

Sort version number on Sql server

I have a table that stores versions 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 result is

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 a simple ordering doesn't 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

+5
source share
7 answers

Brain Solution Implementation

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
+1
source

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

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

, SQL Server . , . (-) .

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
+5

SQL Server 2005 , :

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)

, . ( , @Brian , .)

+1

, .., , UDF, "0" ,

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

0

The SELECT query is sorted alphabetically, not numbers. Without a stored procedure to add leading zeros to individual components of a version number to make them the same length, you cannot (AFAIK) do this in SQL.

0
source

Check it out will help you

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

0
source

More articles:


All Articles