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

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

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

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

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

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  lt  gt  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

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

[oracle] Multiple REPLACE function in Oracle

Examples related to oracle

Examples related to replace