Michael Piankov Michael Piankov - 1 month ago 16
SQL Question

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?

Answer

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.