oracle多个值进行替换,如何在Oracle中替换多个字符串

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

set define off;

drop table fg_rulez

create table fg_rulez as

select 1 id,'

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

所以，单个replace。

结果：

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

的Oracle 11gR2的