Sort a Version-String with pure SQL

(Image by Andrew Martin from Pixabay – modifications by Sam)

Ever tried to sort Version-Strings like this with SQL – for example when you want to find the highest Flyway version installed in your database (installed_rank doesn’t necessarily mean it’s the highest version)?

1.5.185.3
1.5.0
1.5
12.4.1
1.6
2

While there might be different approaches with PL/SQL that result in fewer code, it is achievable in pure SQL.

My (longish) solution with recursive WITH-clause

The core of this solution is to split the version-string into separate rows by “.” delimiter.

After that, we can calculate a comparison-string with leading zeros for each version part and use listagg to group it together again:

with
  input as (
    select '1.5.185.3' as version from dual union all
    select '1.5.0'     as version from dual union all
    select '1.5'       as version from dual union all
    select '12.4.1'    as version from dual union all
    select '1.6'       as version from dual union all
    select '2'         as version from dual
  ),
  -- Known Split-solution with fixed delimiter "."
  split_positions(version, depth, start_pos, end_pos) as (
    select
      version     as version,
      1           as depth,
      1           as start_pos,
      coalesce(
        nullif(instr(version, '.', 1, 1),0),
        length(version)+1
      )           as end_pos
    from input
    union all
    select
      version     as version,
      depth+1 	  as depth,
      end_pos+1   as start_pos,
      coalesce(
        nullif(instr(version, '.', 1, depth+1),0),
        length(version)+1
      )           as end_pos
    from split_positions
    where instr(version, '.', start_pos) > 0
  ),
  split as (
    select
      version,
      depth,
      substr(version, start_pos, end_pos-start_pos) as elem_value
    from split_positions
  ),
  /* Now we calculate a comparison-string for each depth
     The comparison string should have the char-length of the
     longest element in all of the versions, filled with 0 at
     the beginning */
  comparison_elments as (
    select
      version,
      depth,
      lpad(
        elem_value,
        max(length(elem_value)) over (),
        '0'
      ) as comparison_part
    from split
  ),
  /* Now we can listagg all the comparison-strings per version
     and order the strings by depth. That way we get a comparable
     and therefore sortable string */
  comparison_grouped as (
    select
      version,
      listagg(comparison_part) within group ( order by depth ) sort_value
      from comparison_elments
      group by version
  )
select version
  from comparison_grouped
  order by sort_value

You can try it out on LiveSQL as usual.

And if you want to know what the highest Flyway version in your Oracle database is, change the above example a tiny little bit:

with
  input as ( select "version" as version from "schema_version"),
  ...
select version
from comparison_grouped
order by sort_value desc
fetch first row only

Happy coding!

Regexp-Solution by Laurent Schneider

When you learn in public, there is always the chance for someone to come around with a more elegant or already known solution – and that is an immensely great thing!

Matthias Rogel shared this solution to the problem which was discussed in an AskTom thread 10 years ago by Laurent Schneider – and it’s very, very elegant:

with
	input as (
		select '1.5.185.3' as version from dual union all
		select '1.5.0'     as version from dual union all
		select '1.5'       as version from dual union all
		select '12.4.1'    as version from dual union all
		select '1.6'       as version from dual union all
		select '2'         as version from dual
	  )
select
    version
from input
order by
	regexp_replace(
	    replace('.'||version, '.', '.0000000'),
	    '\.0+([0-9]{8})',
	    '.\1'
	)