string 如何在 Oracle 中一起替换多个字符串

how to replace multiple strings together in Oracle

提问by RBS

I have a string coming from a table like "can no pay{1},as your payment{2}due on {3}". I want to replace {1} with some value , {2} with some value and {3} with some value .

我有一个来自表的字符串，例如“can no pay{1},as your payment{2}due on {3}”。我想用某个值替换 {1}，用某个值替换 {2}，用某个值替换 {3}。

Is it Possible to replace all 3 in one replace function ? or is there any way I can directly write query and get replaced value ? I want to replace these strings in Oracle stored procedure the original string is coming from one of my table I am just doing select on that table

是否可以在一个替换功能中替换所有 3 个？或者有什么方法可以直接编写查询并获得替换值？我想在 Oracle 存储过程中替换这些字符串原始字符串来自我的一个表我只是在该表上进行选择

and then I want to replace {1},{2},{3} values from that string to the other value that I have from another table

然后我想将该字符串中的 {1},{2},{3} 值替换为另一个表中的另一个值

回答by hamishmcn

Although it is not one call, you can nest the replace()calls:

虽然不是一个调用，但是可以嵌套replace()调用：

SET mycol = replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)

回答by Florin Ghita

If there are many variables to replace and you have them in another table and if the number of variables is variable you can use a recursive CTE to replace them. An example below. In table fg_rulez you put the strings with their replacement. In table fg_data you have your input strings.

如果有很多变量要替换并且您将它们放在另一个表中，并且变量的数量是可变的，则可以使用递归 CTE 来替换它们。下面举个例子。在表 fg_rulez 中，您将字符串与其替换一起放置。在表 fg_data 中，您有输入字符串。

set define off;
drop table fg_rulez
create table fg_rulez as 
  select 1 id,'<' symbol, 'less than' text from dual
  union all select 2, '>', 'great than' from dual
  union all select 3, '$', 'dollars' from dual
  union all select 4, '&', 'and' from dual;
drop table fg_data;
create table fg_Data AS(
   SELECT 'amount $ must be < 1 & > 2' str FROM dual
   union all
   SELECT 'John is >  Peter & has many $' str FROM dual
   union all
   SELECT 'Eliana is < mary & do not has many $' str FROM dual

   );


WITH  q(str, id) as (
  SELECT str, 0 id 
  FROM fg_Data 
     UNION ALL
  SELECT replace(q.str,symbol,text), fg_rulez.id
  FROM q 
  JOIN fg_rulez 
    ON q.id = fg_rulez.id - 1
)
SELECT str from q where id = (select max(id) from fg_rulez);

So, a single replace.

因此，单个replace.

Result:

结果：

amount dollars must be less than 1 and great than 2 
John is great than Peter and has many dollars 
Eliana is less than mary and do not  has many dollars

The terminology symbol instead of variable comes from this duplicated question.

术语符号而不是变量来自这个重复的问题。

Oracle 11gR2

甲骨文 11gR2

回答by Ricardo Arnold

If the number of values to replace is too big or you need to be able to easily maintain it, you could also split the string, use a dictionary table and finally aggregate the results

如果要替换的值太多或者你需要能够轻松维护它，你也可以拆分字符串，使用字典表，最后聚合结果

In the example below I'm assuming that the words in your string are separated with blankspaces and the wordcount in the string will not be bigger than 100 (pivot table cardinality)

在下面的示例中，我假设您的字符串中的单词用空格分隔，并且字符串中的字数不会大于 100（数据透视表基数）

with Dict as
 (select '{1}' String, 'myfirstval' Repl from dual
   union all
  select '{2}' String, 'mysecondval' Repl from dual
   union all
  select '{3}' String, 'mythirdval' Repl from dual
   union all  
  select '{Nth}' String, 'myNthval' Repl from dual  

 )
,MyStrings as
 (select 'This  is the first example {1} ' Str, 1 strnum from dual
  union all
  select 'In the Second example all values are shown {1} {2} {3} {Nth} ', 2  from dual
  union all
  select '{3} Is the value for the third', 3 from dual
  union all
  select '{Nth} Is the value for the Nth', 4 from dual  
  )
,pivot as (
  Select Rownum Pnum
  From dual
  Connect By Rownum <= 100   
  )
,StrtoRow as
(
SELECT rownum rn
      ,ms.strnum
      ,REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) TXT
  FROM MyStrings ms
      ,pivot pv
where REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) is not null
)
Select Listagg(NVL(Repl,TXT),' ') within group (order by rn) 
from
(
Select sr.TXT, d.Repl, sr.strnum, sr.rn
  from StrtoRow sr
      ,dict d
 where sr.TXT = d.String(+) 
order by strnum, rn 
) group by strnum

回答by Olc

Let's write the same sample as a CTE only:

让我们仅编写与 CTE 相同的示例：

with fg_rulez as (
  select 1 id,'<' symbol, 'less than' text from dual
  union all select 2, '>', 'greater than' from dual
   union all select 3, '$', 'dollars' from dual
  union all select 4, '+', 'and' from dual
),  fg_Data AS (
   SELECT 'amount $ must be < 1 + > 2' str FROM dual
   union all
   SELECT 'John is > Peter + has many $' str FROM dual
   union all
   SELECT 'Eliana is < mary + do not has many $' str FROM dual
), q(str, id) as (
  SELECT str, 0 id 
  FROM fg_Data 
     UNION ALL
  SELECT replace(q.str,symbol,text), fg_rulez.id
  FROM q 
  JOIN fg_rulez 
    ON q.id = fg_rulez.id - 1
)
SELECT str from q where id = (select max(id) from fg_rulez);

回答by Mitchel Sellers

If you are doing this inside of a select, you can just piece it together, if your replacement values are columns, using string concatenation.

如果您在选择中执行此操作，则可以将其拼凑在一起，如果您的替换值是列，则使用字符串连接。