By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 |
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | 0 |
create table mytable (col varchar2(100));
begin
insert into mytable (col) values ('1');
insert into mytable (col) values ('2');
insert into mytable (col) values ('A3');
insert into mytable (col) values ('A3.1');
insert into mytable (col) values ('A3.1.1');
insert into mytable (col) values ('B3');
insert into mytable (col) values ('B3.1');
insert into mytable (col) values ('4.1');
insert into mytable (col) values ('4.1.1');
insert into mytable (col) values ('4.A1.1');
insert into mytable (col) values ('4.A1.A1');
insert into mytable (col) values ('4.1A.B1');
insert into mytable (col) values ('4.1.2');
insert into mytable (col) values ('4.1.10');
end;
/
1 rows affected
select
col,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(col,
'([[:alpha:]])([[:digit:]]+)',
'\2\1'
),
'([[:digit:]]+)',
'00\1'
),
'0([[:digit:]]{4,4})',
'\1'
),
'0([[:digit:]]{3,3})',
'\1'
) as sortkey
from mytable
order by sortkey;
| COL | SORTKEY |
|---|---|
| 1 | 001 |
| 2 | 002 |
| A3 | 003A |
| A3.1 | 003A.001 |
| A3.1.1 | 003A.001.001 |
| B3 | 003B |
| B3.1 | 003B.001 |
| 4.1 | 004.001 |
| 4.1.1 | 004.001.001 |
| 4.1.2 | 004.001.002 |
| 4.1.10 | 004.001.010 |
| 4.A1.1 | 004.001A.001 |
| 4.A1.A1 | 004.001A.001A |
| 4.1A.B1 | 004.001A.001B |