CODEWITHSUNDEEP
sqloracle-database
Michael Piankov
Michael Piankov

Reputation: 1997

Order versions as numbers

I have a table with file-names and version with subversion of files separated by .. 

FNAME, VERSION 
A      0.0.10
B      10.12.412

-- For example
create table file_versions as 
select chr(mod(level,13)+65) as fname
     , decode(mod(level,99),0, '0',
          mod(level,10)||'.'||mod(level,500)||'.'||mod(level,14)
       )
      as version 
  from dual connect by level < 1001;

I'd like to order files by version, but use versions as numbers

select fname, version from file_versions 
 order by fname, version 

FNAME, VERSION 
A      0.0.10
A      0.0.6
...

I'd like don't think about subversion level(there may be one number (0) or more(1.23.14123)). How should I write order by statement ?

I may write something like: 

select fname, version from file_versions 
 order by fname
        , to_number(substr(version, 1, instr(version, '.',1,1)-1))
        , to_number(substr(version, instr(version, '.',1,1)+1, instr(version, '.',1,2)-instr(version, '.',1,1)-1))
        , to_number(substr(version, instr(version, '.',1,2)+1))

But its not so good and will not work if one digit was added to the version string (e.g. 0.0.0.123). Is there a better solution?

Upvotes: 1

Views: 80

Answers (3)

David Aldridge
David Aldridge

Reputation: 52336

More for fun than as a serious suggestion, here's an alternative to parsing the string -- treating the version numbers as inet addresses.

Trickier when you have three levels in your version, but trivial for four levels:

Starting with the idea of:

select a.i::varchar
from (select '192.168.100.128'::inet i union 
      select '22.168.100.128'::inet) a 
order by 1;

        i          
--------------------
 192.168.100.128/32
 22.168.100.128/32
(2 rows)

So for three-level versions you can:

with
  versions as (
    select '1.12.1' v union 
    select '1.3.100'),
  inets as (
    select (v||'.0')::inet i
    from   versions)
select substr(i::varchar,1,length(i::varchar)-5)
from inets
order by i;

 substr  
---------
 1.3.100
 1.12.1
(2 rows)

Maybe everyone should have four level versions ...

Upvotes: 1

Zhenora
Zhenora

Reputation: 68

You may use two regexp first for enhance you group to add 5 zeros to any group. And another one to take last 5 digits from each group. And you get constant length rows and be able to sort it as chars. 

with s(txt) as (select '1' from dual 
             union all 
            select '1.12' from dual 
             union all 
            select '1.12.410' from dual 
             union all 
            select rpad('1.12.410',401,'.03') from dual 
             union all 
            select rpad('1.12.410',401,'.03')||'.01' from dual 
             union all 
            select rpad('1.12.410',401,'.03')||'.02' from dual               
) 
select txt,regexp_replace(regexp_replace(txt, '(\d+)','00000\1'),'\d+ (\d{5})','\1') from s 
order by regexp_replace(regexp_replace(txt, '(\d+)','00000\1'),'\d+(\d{5})','\1')

It will work up to 99999 version or subversion.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use regexp_substr():

order by fname,
         cast(regexp_substr(version, '[^.]+', 1, 1) as number),
         cast(regexp_substr(version, '[^.]+', 1, 2) as number),
         cast(regexp_substr(version, '[^.]+', 1, 3) as number)

Upvotes: 3

Related Questions