ORDER BY on a string containing a "version" number as string with any number of major.minor.subminor levels
03/07/20 19:47
Sorting on strings like "1.2.4", 1.10.1", … will not give the expected result… how to do it ?
Solution working for version string containing only digits and the DOT:
order by regexp(version,'(\d+)',lpad('\1',9,'0'))
And for the fun:
order by (select listagg(lpad(column_value,9, '0'), '.') within group(order by rownum) from xmltable(replace(version, '.',',')))
values.
order by (select listagg(substr('0000000000' || regexp_substr(version,'[^.—]',1,level),-9),'.') within group(order by rownum) from dual connect by regexp_substr(version,'[^.—]',1,level) is not null)
You may also add UPPER/LOWER to make it case insensitive.