I already wrote about Version-String handling in the past, and because it is a topic that I have to deal with regularly (we are using a migration-based delivery strategy with version numbering), I finally wrote a little utility package to make life easier.
It looks as follows:
Spec
create or replace package utl_versionstring as
/**
Make a versionstring sortable
i_version a .-delimited version string
i_fragment_length Length of the resulting fragments, default 8
*/
function sortable(
i_version in varchar2,
i_fragment_length in integer default 8
) return varchar2 deterministic ;
/**
Make a versionstring sortable and cut it at a max depth
i_version a .-delimited version string
i_max_depth The maximum number of fragments to return
i_fragment_length Length of the resulting fragments, default 8
*/
function sortable_cut(
i_version in varchar2,
i_max_depth in integer,
i_fragment_length in integer default null
) return varchar2 deterministic ;
/**
Normalizes a versionstring into a readable format. Removes all the leading 0s of each fragment
i_version a .-delimited version string
*/
function readable(
i_version in varchar2
) return varchar2 deterministic ;
end;
/Body
create or replace package body utl_versionstring as
function sortable(
i_version in varchar2,
i_fragment_length in integer default 8
) return varchar2 deterministic
as
lc_fragment_length constant integer :=
case
when nvl(i_fragment_length, 0) <= 0 then 8
else i_fragment_length
end;
begin
if i_version is null then return null; end if;
return
regexp_replace(
replace('.'||i_version, '.', rpad('.', lc_fragment_length+1, '0')),
'\.0+([0-9]{'||lc_fragment_length||'})',
'.\1'
);
end;
function sortable_cut(
i_version in varchar2,
i_max_depth in integer,
i_fragment_length in integer default null
) return varchar2 deterministic
as
begin
return
regexp_replace(
sortable(i_version, i_fragment_length),
'^((\.[0-9]+){1,' || i_max_depth || '}).*',
'\1'
);
end;
function readable(
i_version in varchar2
) return varchar2 deterministic
as
begin
return
substr(
regexp_replace(
-- add leading delimiter if it doesn't have one
regexp_replace(i_version, '^([^|\.])', '.\1'),
'\.[0]+([0-9]*)',
'.\1'
),
2
);
end;
end;
/You can find the whole package (and accompanying unit-tests) in the SithDB repo.
Sorting done simple
Sorting any list of versionstrings or formatting them with different leading 0s is now dead simple:
with
input as (
select '1.5.185.3' as version from dual union all
select '1.5.001' as version from dual union all
select '1.5' as version from dual union all
select '1.5.18.29' as version from dual union all
select '12.4.1' as version from dual union all
select '2.04.1' as version from dual union all
select '2.4.3' as version from dual union all
select '1.6' as version from dual union all
select '2' as version from dual
)
select
version,
utl_versionstring.sortable(version) version_2zero
from input
order by utl_versionstring.sortable(version);| Version | Version 2 Zero |
|---|---|
| 1.5.001 | .01.05.01 |
| 1.5.18.29 | .01.05.18.29 |
| 1.5.185.3 | .01.05.185.03 |
| 1.6 | .01.06 |
| 2 | .02 |
| 2.04.1 | .02.04.01 |
| 2.4.3 | .02.04.03 |
| 12.4.1 | .12.04.01 |
As is normalizing the version into some readable format with no zeros at all:
with input...
select
utl_versionstring.readable(version) version
from input
order by utl_versionstring.sortable(version);| Version |
|---|
| 1.5 |
| 1.5.1 |
| 1.5.18.29 |
| 1.5.185.3 |
| 1.6 |
| 2 |
| 2.4.1 |
| 2.4.3 |
| 12.4.1 |
Fun with Grouping
But what if we want to know the max version?
with input...
select
utl_versionstring.readable(
max(utl_versionstring.sortable(version))
) as max_version
from input;| Max Version |
|---|
| 12.4.1 |
Or if we want to know what major versions we have and how many versions belong to each major version?
with input...
select
utl_versionstring.readable(
utl_versionstring.sortable_cut(version, 1)
) as version,
count(*) number_of_versions
from input
group by rollup ( utl_versionstring.sortable_cut(version, 1) )
order by utl_versionstring.sortable_cut(version, 1)| Major Version | Number of Versions |
|---|---|
| 1 | 5 |
| 2 | 3 |
| 12 | 1 |
| 9 |
Or the latest version of each major version?
with input...
select
utl_versionstring.readable(
utl_versionstring.sortable_cut(version, 1)
) as major_version,
utl_versionstring.readable(
max(utl_versionstring.sortable(version))
) as latest_version
from input
group by ( utl_versionstring.sortable_cut(version, 1) )
order by utl_versionstring.sortable_cut(version, 1)| Major Version | Latest Version |
|---|---|
| 1 | 1.6 |
| 2 | 2.4.3 |
| 12 | 12.4.1 |
Have fun!
Remember – SQL is your friend!
0 Comments