将字符串中的一个字符替换为循环中的多个字符-ORACLE

Question

我有一种情况，比如一个字符串有一个可替换的字符。。例如

Thi[$] is a strin[$] I am [$]ew to Or[$]cle

现在我需要用s,g,n,a替换[$]分别地

我该怎么做？请帮忙。

Answer 1

有一个特殊的PL/SQL函数UTL_LMS.FORMAT_MESSAGE:

您可以在INLINE pl/sql函数中使用它：

with function format(
str in varchar2
,s1 in varchar2 default null
,s2 in varchar2 default null
,s3 in varchar2 default null
,s4 in varchar2 default null
,s5 in varchar2 default null
,s6 in varchar2 default null
,s7 in varchar2 default null
,s8 in varchar2 default null
,s9 in varchar2 default null
,s10 in varchar2 default null
) return varchar2 
as
begin
return utl_lms.format_message(replace(str,'[$]','%s'),s1,s2,s3,s4,s5,s6,s7,s8,s9,10);
end;
select format('Thi[$] is a strin[$] I am [$]ew to Or[$]cle', 's','g','n','a') as res
from dual;

结果：

RES
-------------------------------------
This is a string I am new to Oracle

Answer 2

这里是一个手动的解决方案，使用递归WITH子句、INSTR和SUBSTR函数来剪切字符串，并在每个节点注入相关的字母。

with rcte(str, sigils, occ) as (
select 'Thi[$] is a strin[$] I am [$]ew to Or[$]cle' as str
, 'sgna' as sigils
, 0 as occ 
from dual
union all
select substr(str, 1, instr(str,'[$]',1,1)-1)||substr(sigils, occ+1, 1)||substr(str, instr(str,'[$]',1,1)+3) as str
, sigils
, occ+1 as occ
from rcte
where occ <= length(sigils)
)
select * 
from rcte
where occ = length(sigils)

这是一个关于db<gt；不停摆弄

然而，@sayarm似乎提供了一个更整洁的解决方案。

Answer 3

考虑一下这个让查找值基于表的方法。请参阅中的注释。使用占位符作为分隔符将原始字符串拆分为多行。然后，使用listagg将行放回一起，并按其顺序连接到查找表。

使用任意多个占位符驱动的表。当然，顺序和其他答案一样重要。

-- First CTE just sets up source data
WITH tbl(str) AS (
SELECT 'Thi[$] is a strin[$] I am [$]ew to Or[$]cle' FROM dual
),
-- Lookup table.  Does not have to be a CTE here, but a normal table
-- in the database.
tbl_sub_values(ID, VALUE) AS (
SELECT 1, 's' FROM dual UNION ALL
SELECT 2, 'g' FROM dual UNION ALL
SELECT 3, 'n' FROM dual UNION ALL
SELECT 4, 'a' FROM dual
),
-- Split the source data using the placeholder as a delimiter
tbl_split(piece_id, str) AS (
SELECT LEVEL AS piece_id, REGEXP_SUBSTR(t.str, '(.*?)([$]|$)', 1, LEVEL, NULL, 1) 
FROM tbl T
CONNECT BY LEVEL <= REGEXP_COUNT(t.str, '[$]') + 1
)
-- select * from tbl_split;
-- Put the string back together, joining with the lookup table
SELECT LISTAGG(str||tsv.value) WITHIN GROUP (ORDER BY piece_id) STRING
FROM tbl_split ts
LEFT JOIN tbl_sub_values tsv
ON ts.piece_id = tsv.id;
STRING                                                                          
--------------------------------------------------------------------------------
This is a string I am new to Oracle