Is there any way to add a unique identifier to every replacement that REGEXP_REPLACE performs?

Question

I have a large text-CLOB that needs some converting done.

A lot of the lines in my CLOB are preceded by a variable name in brackets like so:

[VARIABLE_NAME_ONE] variable_one = 1 + variable_two;

[VARIABLE_NAME_TWO] variable_two = 2 + variable_three;

[VARIABLE_NAME_ONE] variable_one = variable_four - 4;

The problem is that some of the variable names in brackets are not unique, but they need to be unique after I'm done converting.

What I would like is to extend all the variable names in brackets with something like a counter, in order to ensure uniqueness. Because of the brackets, my initial thought was a simple regexp_replace, but is there any way to incorporate a counter in that?

To complete my explanation, I would like the previous example lines converted into this:

[VARIABLE_NAME_ONE_1] variable_one = 1 + variable_two;

[VARIABLE_NAME_TWO_2] variable_two = 2 + variable_three;

[VARIABLE_NAME_ONE_3] variable_one = variable_four - 4;

Answer 1

You can use hierarchical query through splitting by semi-colons by REGEXP_SUBSTR while replacing the values just before the square brackets. And then combine the pieces by LISTAGG() function

UPDATE tab 
   SET col = (
              WITH t AS
              (
                 SELECT REPLACE(REGEXP_SUBSTR(col,'[^;]+',1,level),']','_'||level||']') 
                     AS col, level AS lvl
                  FROM TAB t
                CONNECT BY level <= REGEXP_COUNT(col,';')
              )
              SELECT LISTAGG(col,';') WITHIN GROUP (ORDER BY lvl)||';'
                FROM t) 

Demo