Multiple REPLACE function in Oracle

Question

I am using the REPLACE function in oracle to replace values in my string like;

 SELECT REPLACE('THE NEW VALUE IS #VAL1#','#VAL1#','55') from dual

So this is OK to replace one value, but what about 20+, should I use 20+ REPLACE function or is there a more practical solution.

All ideas are welcome.

Answer 1

The accepted answer to how to replace multiple strings together in Oracle suggests using nested REPLACE statements, and I don't think there is a better way.

If you are going to make heavy use of this, you could consider writing your own function:

CREATE TYPE t_text IS TABLE OF VARCHAR2(256);

CREATE FUNCTION multiple_replace(
  in_text IN VARCHAR2, in_old IN t_text, in_new IN t_text
)
  RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);
BEGIN
  IF( in_old.COUNT <> in_new.COUNT ) THEN
    RETURN in_text;
  END IF;
  v_result := in_text;
  FOR i IN 1 .. in_old.COUNT LOOP
    v_result := REPLACE( v_result, in_old(i), in_new(i) );
  END LOOP;
  RETURN v_result;
END;

and then use it like this:

SELECT multiple_replace( 'This is #VAL1# with some #VAL2# to #VAL3#',
                         NEW t_text( '#VAL1#', '#VAL2#', '#VAL3#' ),
                         NEW t_text( 'text', 'tokens', 'replace' )
                       )
FROM dual

This is text with some tokens to replace

If all of your tokens have the same format ('#VAL' || i || '#'), you could omit parameter in_old and use your loop-counter instead.

Answer 2

Bear in mind the consequences

SELECT REPLACE(REPLACE('TEST123','123','456'),'45','89') FROM DUAL;

will replace the 123 with 456, then find that it can replace the 45 with 89. For a function that had an equivalent result, it would have to duplicate the precedence (ie replacing the strings in the same order).

Similarly, taking a string 'ABCDEF', and instructing it to replace 'ABC' with '123' and 'CDE' with 'xyz' would still have to account for a precedence to determine whether it went to '123EF' or ABxyzF'.

In short, it would be difficult to come up with anything generic that would be simpler than a nested REPLACE (though something that was more of a sprintf style function would be a useful addition).