REGEXP_REPLACE
create table tq84_ora_54002 (
mm_yy varchar2(5),
yyyy_mm as (
regexp_replace(mm_yy, '^(\d\d)\.(\d\d)$', '20\2 \1')
)
);
create or replace function tq84_regexp_replace(
val varchar2,
regex varchar2,
subst varchar2
) return varchar2
DETERMINISTIC
authid definer
as
begin
return regexp_replace(val, regex, subst);
end tq84_regexp_replace;
/
With this function, it is possible to create the table:
create table tq84_ora_54002 (
mm_yy varchar2(5),
yyyy_mm as (
tq84_regexp_replace(mm_yy, '^(\d\d)\.(\d\d)$', '20\2 \1')
)
);
Test data:
begin
insert into tq84_ora_54002(mm_yy) values ('01.13');
insert into tq84_ora_54002(mm_yy) values ('04.18');
insert into tq84_ora_54002(mm_yy) values ('09.17');
commit;
end;
/
select * from tq84_ora_54002;
Cleaning up
drop table tq84_ora_54002;