hanahana-sql-script

REGEXPR_REPLACE string

How to replace & in the below string:

'extends the functionality & of the & REPLACE function & by'

with one, two and three respectively.

In the end the result should be:

extends the functionality one of the two REPLACE function three by
Solution

  • You could do a nested REPLACE_REGEXPR function and always replace the first (next) remaining match with a different string.

    SELECT REPLACE_REGEXPR
       ('&' IN REPLACE_REGEXPR 
               ('&' IN REPLACE_REGEXPR
                      ('&' IN 'extends the functionality & of the & REPLACE function & by'
                        WITH 'one' OCCURRENCE 1) 
                 WITH 'two' OCCURRENCE 1)  
         WITH 'three' OCCURRENCE 1) "replace_regexpr" 
FROM DUMMY;