ORDER BY on a string containing a "version" number as string with any number of major.minor.subminor levels

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, '.',',')))

This works for VERSION strings containing only numbers and with dot as the only separator. It also fails on NULL


You may want to use:

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)

To support VERSION strings containing both . and - as separator and with alpha numeric values, e.g. '1.3-alpha'.

You may also add UPPER/LOWER to make it case insensitive.