Oracle 中的多个 REPLACE 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2947623/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple REPLACE function in Oracle
提问by Adnan
I am using the REPLACEfunction in oracle to replace values in my string like;
我正在使用REPLACEoracle 中的函数来替换字符串中的值，例如；
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+ REPLACEfunction or is there a more practical solution.
所以这可以替换一个值，但是 20+ 呢，我应该使用 20+REPLACE函数还是有更实用的解决方案。
All ideas are welcome.
欢迎所有想法。
回答by user3270011
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.
即使这个线程是旧的，它也是 Google 上的第一个，所以我将使用正则表达式发布一个与此处实现的函数等效的 Oracle。
Is fairly faster than nested replace(), and much cleaner.
比嵌套的 replace() 快得多，而且更干净。
To replace strings 'a','b','c' with 'd' in a string column from a given table
将给定表的字符串列中的字符串 'a','b','c' 替换为 'd'
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!
当心正则表达式特殊字符！
回答by Peter Lang
The accepted answer to how to replace multiple strings together in Oraclesuggests using nested REPLACEstatements, and I don't think there is a better way.
如何在 Oracle 中将多个字符串替换在一起的公认答案建议使用嵌套REPLACE语句，我认为没有更好的方法。
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_oldand use your loop-counter instead.
如果所有令牌都具有相同的格式 ( '#VAL' || i || '#')，则可以省略参数in_old并使用循环计数器代替。
回答by Gary Myers
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).
将 123 替换为 456，然后发现它可以将 45 替换为 89。对于具有等效结果的函数，它必须复制优先级（即以相同的顺序替换字符串）。
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'.
类似地，取一个字符串 'ABCDEF'，并指示它用 '123' 替换 'ABC' 和用 'xyz' 替换 'CDE' 仍然需要考虑优先级，以确定它是转到 '123EF' 还是 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).
简而言之，很难想出任何比嵌套 REPLACE 更简单的泛型（尽管更像是 sprintf 样式函数的东西将是有用的补充）。
回答by TJ Abrahamsen
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:
这是一篇旧帖子，但我最终使用了 Peter Lang 的想法，并采用了类似但又不同的方法。这是我所做的：
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.
我在我的博客上写了一篇关于这个的文章。
回答by Frank Schmitt
In case all your source and replacement strings are just one character long, you can simply use the TRANSLATEfunction:
如果您的所有源字符串和替换字符串都只有一个字符长，您可以简单地使用该TRANSLATE函数：
SELECT translate('THIS IS UPPERCASE', 'THISUP', 'thisup')
FROM DUAL
See the Oracle documentationfor details.
有关详细信息，请参阅Oracle 文档。
回答by Joaquinglezsantos
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.
我已经通过 varchar2 表作为参数创建了一个通用的多替换字符串 Oracle 函数。varchar 将被替换为 table 的位置 rownum 值。
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'));
