Oracle SQL to Sort Version Numbers

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

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.

Oracle DB version column sorting

 SELECT * FROM YOUR_TABLE
 ORDER BY 
 to_number(regexp_substr(COL1, '[^.]+', 1, 1)) DESC NULLS FIRST,
 to_number(regexp_substr(COL1, '[^.]+', 1, 2)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 3)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 4)) DESC NULLS FIRST ;

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

SQL Oracle Sort string (numbers) and (letters with numbers)

select column 
from table
order by 
  regexp_substr(column, '^\D*') nulls first,
  to_number(regexp_substr(column, '\d+'))

fiddle

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

Oracle sql Order By number and char inside

The immediate problem is that you are incrementing the positionas well as the occurrence; so

REGEXP_SUBSTR(string, '\d', 2, 2)

should be

REGEXP_SUBSTR(string, '\d', 1, 2)

But you're overcomplicating it, and not handling multiple-digit elements, either when extracting or when sorting as nothing is treated as a number.

I think this does what you want:

ORDER BY
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) nulls first,
  string

db<>fiddle

---

If only the first element can start with a character (or characters), then you just just add a check for that after the first digit:

ORDER BY
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) nulls first,
  REGEXP_SUBSTR(string, '^\w+', 1, 1) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) nulls first

which will order 3,A3,A3.1,B3.

You might not need the string at the end now.

db<>fiddle

ORACLE Order By number first in string field

When I run your example in my database I get the 'correct' sort order as per your list. Check you NLS_SORT setting; mine is set to BINARY.

Try it out by changing it for the session;

ALTER SESSION SET nls_sort='BINARY';

There are lots of possibilities for this parameter so if you want to experiment you can find the possibilities in the V$NLS_VALID_VALUES view.

I've also just spotted that you can do the following;

SELECT * FROM table1 ORDER BY colb, NLSSORT(cola, 'NLS_SORT=BINARY')

---

Related Topics