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

User · Answer

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.

User · Answer

I have created a general multi replace string Oracle function by a table of varchar2 as parameter  The varchar will be replaced for the position rownum value of table   For example   Text  Hello  0   this is a  2  for  1  Parameters  TABLE  world   all   message     Returns   Hello world  this is a message for all    You must create a type   CREATE OR REPLACE TYPE  TBL VARCHAR2  IS TABLE OF VARCHAR2 250     The funcion is   CREATE OR REPLACE FUNCTION FN REPLACETEXT      pText IN VARCHAR2       pPar IN TBL VARCHAR2   RETURN VARCHAR2 IS     vText VARCHAR2 32767       vPos INT      vValue VARCHAR2 250        CURSOR cuParameter POS INT  IS     SELECT VAL         FROM                           SELECT VAL  ROWNUM AS RN              FROM                     SELECT COLUMN VALUE VAL                   FROM TABLE pPar                                            WHERE RN POS 1  BEGIN     vText    pText      FOR i IN 1  REGEXP COUNT pText       0-9        LOOP         vPos    TO NUMBER SUBSTR REGEXP SUBSTR pText       0-9       1 i  2  LENGTH REGEXP SUBSTR pText       0-9       1 i   - 2             OPEN cuParameter vPos           FETCH cuParameter INTO vValue          IF cuParameter FOUND THEN             vText    REPLACE vText  REGEXP SUBSTR pText       0-9       1 i   vValue           END IF          CLOSE cuParameter      END LOOP       RETURN vText   EXCEPTION       WHEN OTHERS       THEN          RETURN pText  END FN REPLACETEXT      Usage   TEXT RETURNED    FN REPLACETEXT  Hello  0   this is a  2  for  1    TBL VARCHAR2  world   all   message

User · Answer

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

User · Answer

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

User · Answer

In case all your source and replacement strings are just one character long  you can simply use the TRANSLATE function     SELECT translate  THIS IS UPPERCASE    THISUP    thisup      FROM DUAL   See the Oracle documentation for details

User · Answer

This is an old post  but I ended up using Peter Lang s thoughts  and did a similar  but yet different approach  Here is what I did   CREATE OR REPLACE FUNCTION multi replace                          pString IN VARCHAR2                          pReplacePattern IN VARCHAR2   RETURN VARCHAR2 IS     iCount  INTEGER      vResult VARCHAR2 1000       vRule   VARCHAR2 100       vOldStr VARCHAR2 50       vNewStr VARCHAR2 50   BEGIN     iCount    0      vResult    pString      LOOP         iCount    iCount   1           -- Step   1  Pick out the replacement rules         vRule    REGEXP SUBSTR pReplacePattern           1  iCount            -- Step   2  Pick out the old and new string from the rule         vOldStr    REGEXP SUBSTR vRule           1  1           vNewStr    REGEXP SUBSTR vRule           1  2            -- Step   3  Do the replacement         vResult    REPLACE vResult  vOldStr  vNewStr            EXIT WHEN vRule IS NULL      END LOOP       RETURN vResult  END multi replace    Then I can use it like this   SELECT  multi replace                           This is a test string with a    a   character  and finally a  amp  character                               23    24  amp   25            FROM dual   This makes it so that I can can any character string with any character string   I wrote a post about this on my blog

[oracle] Multiple REPLACE function in Oracle

Examples related to oracle

Examples related to replace