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.
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).
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.
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'));
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.
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.
Source: Stackoverflow.com