Version Number Sorting in SQL Server

SQL sort by version number, a string of varying length

For best results, refactor version number storage so that each section has it's own column: MajorVersion, MinorVersion, Revision, Build. Then the ordering problem suddenly becomes trivial. You can also build a computed column for easy retrieval of the full string.

Version number sorting in Sql Server

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

How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query

If You are using SQL Server 2008

select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);

What is hierarchyid

Edit:

Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.

The challenge

Oracle SQL to Sort Version Numbers

This is one way to do it. First order by the number before . and then by the numbers after .

select version_number 
from mytable 
order by substr(version_number, 1, instr(version_number,'.')-1) desc
        ,length(substr(version_number, instr(version_number,'.')+1)) desc
        ,substr(version_number, instr(version_number,'.')+1) desc

How to sort version numbers (like 5.3.60.8)

I will show here the answer from AskTom, which can be used with different version size :

WITH inputs 
     AS (SELECT 1 as id, '6.0.5.94' as col FROM DUAL
         UNION ALL
         SELECT 2,'5.3.30.8' FROM DUAL
         UNION ALL
         SELECT 3,'5.3.4.8' FROM DUAL
         UNION ALL
         SELECT 4,'3' FROM DUAL
         UNION ALL
         SELECT 5,'3.3.40' FROM DUAL
         UNION ALL
         SELECT 6,'3.3.4.1.5' FROM DUAL
         UNION ALL
         SELECT 7,'3.3.4.1' FROM DUAL)
    SELECT col, MAX (SYS_CONNECT_BY_PATH (v, '.')) p
      FROM (SELECT t.col, TO_NUMBER (SUBSTR (x.COLUMN_VALUE, 1, 5)) r, SUBSTR (x.COLUMN_VALUE, 6) v, id rid
              FROM inputs t,
                   TABLE (
                      CAST (
                         MULTISET (
                                SELECT    TO_CHAR (LEVEL, 'fm00000')
                                       || TO_CHAR (TO_NUMBER (SUBSTR ('.' || col || '.', INSTR ('.' || col || '.', '.', 1, ROWNUM) + 1, INSTR ('.' || col || '.', '.', 1, ROWNUM + 1) - INSTR ('.' || col || '.', '.', 1, ROWNUM) - 1)), 'fm0000000000')
                                  FROM DUAL
                            CONNECT BY LEVEL <= LENGTH (col) - LENGTH (REPLACE (col, '.', '')) + 1) AS SYS.odciVarchar2List)) x)
START WITH r = 1
CONNECT BY PRIOR rid = rid AND PRIOR r + 1 = r
  GROUP BY col
  ORDER BY p

How can I sort version numbers such as ‘A’, ‘B’, ‘AA’, ‘AB’ and ‘AAA’?

Try something like this:

ORDER BY LEN(myValue),myValue

this will order the 1-character, then the 2-character, etc...

how to sort a column which contains Semantic Version in Postgres

Accepted answer does work for sorting, but will include e.g. version "10.1" if querying for e.g version < '2'. See https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ad031218fe4c941f29c9b03de80d54e0.

If, instead, you define the collation on the column, you get the correct results when filtering by version:

CREATE COLLATION en_natural (
  LOCALE = 'en-US-u-kn-true',
  PROVIDER = 'icu'
);

CREATE TABLE test (
  version varchar(20) collate en_natural
);

insert into versions values
('10.1'),
('2.1'),
('1.2.9'),
('1.24'),
('1.23.231+b'),
('1.23.231+a'),
('1.23'),
('1.23.231-test.beta'),
('1.23.231-test.alpha'),

('1.23.45-rc.2+B001'),
('0.9');

SELECT *
FROM versions
WHERE v1 < '2.0'
ORDER BY v1 desc;

-- note results do not include 10.1
1.24
1.23.231+b
1.23.231+a
1.23.231-test.beta
1.23.231-test.alpha
1.23.45-rc.2+B001
1.23
1.2.9
0.9

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a284745f2b55617a964c777c29b7e745

---

Related Topics