By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with input_(val) as (
select '1:::9999' from dual
union all
select '1:2::' from dual
union all
select '1:2::3:5' from dual
)
, replaced as (
select input_.*, replace(val, ' ', '') as val_replaced
from input_
)
select
val,
substr(
val_replaced,
/*Locate the first occurrence of a colon and get a substring ...*/
instr(val_replaced, ':', 1, 3) + 1,
/*.. until the end, if the next colon is absent, or until the next colon*/
nvl(nullif(instr(val_replaced, ':', 1, 4), 0), length(val_replaced) + 1) - instr(val_replaced, ':', 1, 3) - 1
) as col
from replaced
| VAL | COL |
|---|---|
| 1:::9999 | 9999 |
| 1:2:: | null |
| 1:2::3:5 | 3 |
create table test_tab (val) as
select
level
|| ':'
|| decode(mod(level, 20), 0, level)
|| ':'
|| decode(mod(level, 30), 0, level)
|| ':'
|| decode(mod(level, 40), 0, level)
|| decode(mod(level, 50), 0, ':' || level)
from dual
connect by level < 1000000
999999 rows affected
create procedure measure(stmt varchar2) as
st timestamp(6) := systimestamp();
begin
execute immediate stmt;
dbms_output.put_line(to_char(systimestamp() - st));
end;/
begin
measure(q'{
create table t1 as
with replaced as (
select input_.*, replace(val, ' ', '') as val_replaced
from test_tab input_
)
select /*+gather_plan_statistics*/
val,
substr(
val_replaced,
/*Locate the first occurrence of a colon and get a substring ...*/
instr(val_replaced, ':', 1, 3) + 1,
/*.. until the end, if the next colon is absent, or until the next colon*/
nvl(nullif(instr(val_replaced, ':', 1, 4), 0), length(val_replaced) + 1) - instr(val_replaced, ':', 1, 3) - 1
) as col
from replaced }');
dbms_output.put_line('');
end;/
1 rows affected
dbms_output:
+000000000 00:00:02.574358000
begin
measure(q'{create table t2 as
with replaced as (
select input_.*, replace(val, ' ', '') as val_replaced
from test_tab input_
)
select /*+gather_plan_statistics*/
val,
(REGEXP_SUBSTR(val_replaced, '([^: ]*)(:|$)', 1, 4, 'i', 1)) as col
from replaced }');
dbms_output.put_line('');
end;/
1 rows affected
dbms_output:
+000000000 00:00:06.515888000