CODEWITHSUNDEEP
oracle-databasereplace
Adnan
Adnan

Reputation: 26350

Multiple REPLACE function in Oracle

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.

Upvotes: 31

Views: 239754

Answers (8)

Glenn
Glenn

Reputation: 9150

Another example of how to apply an ordered list of character replacements. In this case:

  • convert a backslash to a slash
  • convert a double slash occurrence to a single slash
  • convert a single quote to an escaped single quote

For example, convert from:

\\server\Folder\611891\Joe's Doc.pdf

to

/server/Folder/611891/Joe\'s Doc.pdf

Can use a recursive CTE to iterate through the ordered list in sequence. First part demonstrates how the string evolves:

WITH subs AS (
         SELECT '\' convert_from, '/' convert_to, 1 seq FROM DUAL
   UNION SELECT '//',  '/',   2     FROM DUAL
   UNION SELECT '''',  '\''', 3     FROM DUAL
),

rcte(convert_from, convert_to, seq, src) AS (
  SELECT '', '', 0, '\\server\Folder\611891\Joe''s Doc.pdf' src FROM DUAL
  UNION ALL
  SELECT s.convert_from, s.convert_to, s.seq, REPLACE(r.src, s.convert_from, s.convert_to)
    FROM rcte r
    JOIN subs s ON (s.seq = r.seq + 1)
)

SELECT *
  FROM rcte
  ORDER BY seq
; 

| CONVERT_FROM | CONVERT_TO | SEQ |                                  SRC |
|--------------|------------|-----|--------------------------------------|
|       (null) |     (null) |   0 | \\server\Folder\611891\Joe's Doc.pdf |
|            \ |          / |   1 | //server/Folder/611891/Joe's Doc.pdf |
|           // |          / |   2 |  /server/Folder/611891/Joe's Doc.pdf |
|            ' |         \' |   3 | /server/Folder/611891/Joe\'s Doc.pdf |

But actually only interested in the final result:

SELECT src
  FROM rcte
  WHERE seq = (SELECT MAX(seq) FROM subs)
;

|                                  SRC |
|--------------------------------------|
| /server/Folder/611891/Joe\'s Doc.pdf |

Upvotes: 0

wfolkerts
wfolkerts

Reputation: 107

Thanks for the answer. Rather than specifying the translation in the call, you can also do this using a cursor as shown below.

create or replace function character_substitutions (input_str varchar2)
return varchar2

as

v_result VARCHAR2(4000);

cursor c_translate_table is
    select '&' as symbol_to_replace, 'amp' as symbol_in_return_string from dual
    union all
    select '/' as symbol_to_replace, '_' as symbol_in_return_string from dual
    union all
    select '"' as symbol_to_replace, 'in' as symbol_in_return_string from dual
    union all
    select '%' as symbol_to_replace, 'per' as symbol_in_return_string from dual
    union all
    select '.' as symbol_to_replace, '_' as symbol_in_return_string from dual;
    
begin

v_result := input_str;

for r_translate in c_translate_table loop

    v_result := REPLACE( v_result, r_translate.symbol_to_replace, r_translate.symbol_in_return_string);

end loop;

return v_result;

end;
/

Upvotes: 2

Joaquinglezsantos
Joaquinglezsantos

Reputation: 1620

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'));

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30775

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.

Upvotes: 8

user3270011
user3270011

Reputation: 686

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!

Upvotes: 67

TJ Abrahamsen
TJ Abrahamsen

Reputation: 51

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 & character'
                        ,'#=%23/$=%24/&=%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.

Upvotes: 5

Gary Myers
Gary Myers

Reputation: 35401

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).

Upvotes: 26

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 26

Related Questions