REGEXPR_REPLACE string

Question

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

Answer 1

(A bit late, but) a version using one REPLACE_REGEXPR:

SELECT
    REPLACE_REGEXPR(
        '([^&]*)&([^&]*)&([^&]*)&([^&]*)'
        IN 'extends the functionality & of the & REPLACE function & by'
        WITH '\1one\2two\3three\4') "replace_regexpr"
FROM DUMMY
;

Out:

replace_regexpr                                                    |
-------------------------------------------------------------------|
extends the functionality one of the two REPLACE function three by |

Answer 2

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;