How to add a string on a specific string by using regex_replace method in Oracle

Question

I am trying to add a string '_$' to a index name and a table name as follows. I need to use a method 'regexp_replace' in SELECT statement. select regexp_replace(input_string......)

@ Input

CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMP_NO")

@ Desired Output

CREATE UNIQUE INDEX "SCOTT"."PK_EMP_$" ON "SCOTT"."EMP_$" ("EMP_NO")

Can you help me to build a regular expression for that?

Answer 1

Fairly brute solution would be using the following pattern:

(.*)(" ON ".*)(" \(.*)

with the following replace string:

\1_$\2_$\3

The pattern works by splitting the input in the places where you need to insert the _$ token, and then joining it back placing the tokens in the places we split the input:

CREATE UNIQUE INDEX "SCOTT"."PK_EMP|" ON "SCOTT"."EMP|" ("EMP_NO")

Full SELECT query would look like that:

SELECT REGEXP_REPLACE(
        'CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMP_NO")',
        '(.*)(" ON ".*)(" \(.*)',
        '\1_$\2_$\3'
       ) RX
  FROM dual;