no rule, use for any inbuild function, i have a number of forms and different edition dates so i showing sample data. first, i am matched check the FORM_NO column the last four digits with the Edition_Date column. if matched then the FORM_NO column the last four digits are skipped and concatenations with the Edition_Date column else as it is show.For example when i am trying below query executed.
with FOrm as (select 'GL-GEN-CHANGE-ENDT-MISC-OLD' Form_No, '05-05' Edition_Date from dual
union all
select 'CM59930202' Form_No, '02-02' Edition_Date from dual
union all
select '99109 06 08' Form_No, '06-08' Edition_Date from dual
union all
select 'IM 7978 08 12' Form_No, '08-12' Edition_Date from dual
union all
select 'IL01220907' Form_No, '09-07' Edition_Date from dual
union all
select 'PRG 2023 05 14' Form_No, '05-14' Edition_Date from dual)
select Form_No,case when SUBSTR(REGEXP_REPLACE( Form_No, '[^[:alnum:]]', NULL ),-4)=REGEXP_REPLACE( Edition_Date, '[^[:alnum:]]', NULL )
then substr(Form_No, 0, length(Form_No)-4)||' '||Edition_Date end as REC1,
case when SUBSTR(REGEXP_REPLACE( Form_No, '[^[:alnum:]]', NULL ),-4)=REGEXP_REPLACE( Edition_Date, '[^[:alnum:]]', NULL )
then substr(Form_No, 0, length(REGEXP_REPLACE( Form_No, '[^[:alnum:]]', NULL ))-4)||' '||Edition_Date
when regexp_count(Form_No,'[^ [:alnum:]]')>2 then Form_No||' '||Edition_Date
when regexp_count(Form_No,'[^-[:alnum:]]')>2 then SUBSTR(REGEXP_REPLACE( Form_No, '[^[:alnum:]]', NULL ),-5)||' '||Edition_Date
end REC2 from FOrm
I showed the following output after executing the query
FORM_NO Edition_Date REC1 REC2
---------------------------- ------------- ---------------- --------------------
GL-GEN-CHANGE-ENDT-MISC-OLD 05-05 NULL GL-GEN-CHANGE-ENDT-MISC-OLD 05-05
CM59930202 02-02 CM5993 02-02 CM5993 02-02
99109 06 08 06-08 99109 0 06-08 99109 06-08
IM 7978 08 12 08-12 IM 7978 0 08-12 IM 797 08-12
IL01220907 09-07 IL0122 09-07 IL0122 09-07
PRG 2023 05 14 05-14 PRG 2023 0 05-14 PRG 202 05-14
The expectation result
FORM_NO REC1 REC2
----------------------------------------------------------- ------------------------------------
GL-GEN-CHANGE-ENDT-MISC-OLD GL-GEN-CHANGE-ENDT-MISC-OLD GL-GEN-CHANGE-ENDT-MISC-OLD
CM59930202 CM5993 02-02 CM5993 02-02
99109 06 08 99109 06-08 99109 06-08
IM 7978 08 12 IM7978 08-12 IM 7978 08-12
IL01220907 IL0122 09-07 IL0122 09-07
PRG 2023 05 14 PRG2023 05-14 PRG 2023 05-14
Here is a regex that gives you your wanted output, you didn't mention any rule only wanted output so here it is:
with FOrm as
(
select 'GL-GEN-CHANGE-ENDT-MISC-OLD' Form_No, '05-05' Edition_Date from dual
union all
select 'CM59930202' Form_No, '02-02' Edition_Date from dual
union all
select '99109 06 08' Form_No, '06-08' Edition_Date from dual
union all
select 'IM 7978 08 12' Form_No, '08-12' Edition_Date from dual
union all
select 'IL01220907' Form_No, '09-07' Edition_Date from dual
union all
select 'PRG 2023 05 14' Form_No, '05-14' Edition_Date from dual)
select Form_No,
case
when not regexp_like(Form_No,'[0-9]{1}') then Form_No
when regexp_like(trim(Form_No),'^[A-Za-z]{1,}[[:space:]]{1,}') then
regexp_substr(Form_No,'^[A-Za-z]{1,}')||regexp_substr(Form_No,'[0-9]{1,}')||' '||
Edition_Date
when regexp_like(Form_No,'^[0-9]{1,}') then Form_No
when regexp_like(Form_No,'^[A-Za-z]{1,}[0-9]{1,}') then
regexp_substr(Form_No,'^[A-Za-z]{1,}')||regexp_substr(Form_No,'[0-9]{4}')||' '||
Edition_Date
end as rec1,
case
when not regexp_like(Form_No,'[0-9]{1}') then Form_No
when regexp_like(trim(Form_No),'^[A-Za-z]{1,}[[:space:]]{1,}') then
regexp_substr(Form_No,'^[A-Za-z]{1,}')||' '||regexp_substr(Form_No,'[0-9]{1,}')||' '||
Edition_Date
when regexp_like(Form_No,'^[0-9]{1,}') then Form_No
when regexp_like(Form_No,'^[A-Za-z]{1,}[0-9]{1,}') then
regexp_substr(Form_No,'^[A-Za-z]{1,}')||regexp_substr(Form_No,'[0-9]{4}')||' '||
Edition_Date
end as rec2
from FOrm
RESULT:
FORM_NO REC1 REC2
------------------------------------------------------------------------------------
GL-GEN-CHANGE-ENDT-MISC-OLD GL-GEN-CHANGE-ENDT-MISC-OLD GL-GEN-CHANGE-ENDT-MISC-OLD
CM59930202 CM5993 02-02 CM5993 02-02
99109 06 08 99109 06 08 99109 06 08
IM 7978 08 12 IM7978 08-12 IM 7978 08-12
IL01220907 IL0122 09-07 IL0122 09-07
PRG 2023 05 14 PRG2023 05-14 PRG 2023 05-14