Replace a sentence in ORACLE using REGEXP_REPLACE

Question

I Would like to remove all occurences of the below pattern in my string.

SUPPLEMENTAL LOG GROUP "SAMPLESL" ("PIKEY") ALWAYS, 
SUPPLEMENTAL LOG GROUP "SAMPLE2" ("UIKEY") ALWAYS, 

How do I create a regular expression for this?

Essentially, I do not want any SUPPLEMENTAL LOG GROUP statements. This is generated by dbms_metadata.get_ddl.

Answer 1

You could use:

WITH cte(R) as (select 
'CREATE TABLE TAB (X int)
--- something
SUPPLEMENTAL LOG GROUP "SAMPLESL" ("PIKEY") ALWAYS, 
SUPPLEMENTAL LOG GROUP "SAMPLE2" ("UIKEY") ALWAYS, 
--else
'
  FROM DUAL
)
select R, regexp_replace(R, 'SUPPLEMENTAL LOG GROUP.*', '')
from CTE;

DBFiddle Demo