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

Even if this thread is old is the first on Google, so I'll post an Oracle equivalent to the function implemented here, using regular expressions.

Is fairly faster than nested replace(), and much cleaner.

To replace strings 'a','b','c' with 'd' in a string column from a given table

select regexp_replace(string_col,'a|b|c','d') from given_table 

It is nothing else than a regular expression for several static patterns with 'or' operator.

Beware of regexp special characters!

Answer 2

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.