oracle 多个字符替换实现

<div class="codetitle"><a style="CURSOR: pointer" data="77105" class="copybut" id="copybut77105" onclick="doCopy('code77105')"> 代码如下:<div class="codebody" id="code77105">
create table A_TEST
(
PAYOUT_ITEM_CODE VARCHAR2(30) not null,
FORMULA_DET VARCHAR2(1000)
) create table B_TEST
(
ELEMENT_ID VARCHAR2(5) not null,
NAME VARCHAR2(41)
)
FORMULA_DET列里ELEMENT_ID替换成NAME
测试数据如下
<div class="codetitle"><a style="CURSOR: pointer" data="68819" class="copybut" id="copybut68819" onclick="doCopy('code68819')"> 代码如下:<div class="codebody" id="code68819">
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30228','({30015}+{30016})450');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30102','({30015}+{30016})1500');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})5000');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})2500');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})2300');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})1150');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30104','({30015}+{30016})30012');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30226','{30057}2300');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})3000');
insert into a_test (PAYOUT_ITEM_CODE,'{30006}+{30061}+{30008}');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}380012');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30210','({30030}+{30031}+{30032})38000+{30033}23000');
insert into a_test (PAYOUT_ITEM_CODE,'({30030}+{30031}+{30032}+{30033})38000+{30036}10000');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30229','({30015}+{30016})1400');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30216','({30015}+{30016})1300');
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})650');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30307','({30015}+{30016})360');
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values ('30302','{30051}');
insert into a_test (PAYOUT_ITEM_CODE,'{30052}');
insert into a_test (PAYOUT_ITEM_CODE,'{30053}');
insert into a_test (PAYOUT_ITEM_CODE,'{30054}');
insert into a_test (PAYOUT_ITEM_CODE,'{30055}');
insert into a_test (PAYOUT_ITEM_CODE,'{30056}');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}4000');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}3800');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}10012');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}50012');
insert into a_test (PAYOUT_ITEM_CODE,'{30060}0');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}/{30057}150000');
insert into a_test (PAYOUT_ITEM_CODE,'{30057}*6000');

<div class="codetitle"><a style="CURSOR: pointer" data="46793" class="copybut" id="copybut46793" onclick="doCopy('code46793')"> 代码如下:<div class="codebody" id="code46793">
insert into b_test (ELEMENT_ID,NAME)
values ('30006','a1');
insert into b_test (ELEMENT_ID,NAME)
values ('30008','a2');
insert into b_test (ELEMENT_ID,NAME)
values ('30009','a3');
insert into b_test (ELEMENT_ID,NAME)
values ('30010','a4');
insert into b_test (ELEMENT_ID,NAME)
values ('30015','a5');
insert into b_test (ELEMENT_ID,NAME)
values ('30016','a6');
insert into b_test (ELEMENT_ID,NAME)
values ('30017','a7');
insert into b_test (ELEMENT_ID,NAME)
values ('30018','a8');
insert into b_test (ELEMENT_ID,NAME)
values ('30019','a9');
insert into b_test (ELEMENT_ID,NAME)
values ('30020','a10');
insert into b_test (ELEMENT_ID,NAME)
values ('30021','a11');
insert into b_test (ELEMENT_ID,NAME)
values ('30022','a12');
insert into b_test (ELEMENT_ID,NAME)
values ('30023','a13');
insert into b_test (ELEMENT_ID,NAME)
values ('30024','a14');
insert into b_test (ELEMENT_ID,NAME)
values ('30025','a15');
insert into b_test (ELEMENT_ID,NAME)
values ('30026','a16');
insert into b_test (ELEMENT_ID,NAME)
values ('30027','a17');
insert into b_test (ELEMENT_ID,NAME)
values ('30028','a18');
insert into b_test (ELEMENT_ID,NAME)
values ('30029','a19');
insert into b_test (ELEMENT_ID,NAME)
values ('30030','a20');
insert into b_test (ELEMENT_ID,NAME)
values ('30031','a21');
insert into b_test (ELEMENT_ID,NAME)
values ('30032','a22');
insert into b_test (ELEMENT_ID,NAME)
values ('30033','a23');
insert into b_test (ELEMENT_ID,NAME)
values ('30034','a24');
insert into b_test (ELEMENT_ID,NAME)
values ('30035','a25');
insert into b_test (ELEMENT_ID,NAME)
values ('30036','a26');
insert into b_test (ELEMENT_ID,NAME)
values ('30037','a27');
insert into b_test (ELEMENT_ID,NAME)
values ('30038','a28');
insert into b_test (ELEMENT_ID,NAME)
values ('30039','a29');
insert into b_test (ELEMENT_ID,NAME)
values ('30040','a30');
insert into b_test (ELEMENT_ID,NAME)
values ('30041','a31');
insert into b_test (ELEMENT_ID,NAME)
values ('30042','a32');
insert into b_test (ELEMENT_ID,NAME)
values ('30043','a33');
insert into b_test (ELEMENT_ID,NAME)
values ('30044','a34');
insert into b_test (ELEMENT_ID,NAME)
values ('30045','a35');
insert into b_test (ELEMENT_ID,NAME)
values ('30046','a36');
insert into b_test (ELEMENT_ID,NAME)
values ('30047','a37');
insert into b_test (ELEMENT_ID,NAME)
values ('30048','a38');
insert into b_test (ELEMENT_ID,NAME)
values ('30049','a39');
insert into b_test (ELEMENT_ID,NAME)
values ('30050','a40');
insert into b_test (ELEMENT_ID,NAME)
values ('30051','a41');
insert into b_test (ELEMENT_ID,NAME)
values ('30052','a42');
insert into b_test (ELEMENT_ID,NAME)
values ('30053','a43');
insert into b_test (ELEMENT_ID,NAME)
values ('30054','a44');
insert into b_test (ELEMENT_ID,NAME)
values ('30055','a45');
insert into b_test (ELEMENT_ID,NAME)
values ('30056','a46');
insert into b_test (ELEMENT_ID,NAME)
values ('30057','a47');
insert into b_test (ELEMENT_ID,NAME)
values ('30058','a48');
insert into b_test (ELEMENT_ID,NAME)
values ('30059','a49');
insert into b_test (ELEMENT_ID,NAME)
values ('30060','a50');
insert into b_test (ELEMENT_ID,NAME)
values ('30061','a51');

这个如果用function或者是sp做，就没有什么难度了。
但是用sql做就比较难度了
<div class="codetitle"><a style="CURSOR: pointer" data="86314" class="copybut" id="copybut86314" onclick="doCopy('code86314')"> 代码如下:<div class="codebody" id="code86314">
select gid,payout_item_code,formula_det,max(substr(txt,1,length(txt)-1)) from (
select a.gid,
a.payout_item_code,
a.formula_det,
replace(sys_connect_by_path(decode(b.element_id,null,a.signal,replace(signal,b.element_id,b.name)),'##'),'##','') txt
from
(select gid,row_number() over(partition by gid order by level) rn,
substr(formula_det,decode(rownum-(allcnt-selfcnt),instr(formula_det,'}',rownum-(allcnt-selfcnt)-1)+1),rownum-(allcnt-selfcnt))-decode(rownum-(allcnt-selfcnt),rownum-(allcnt-selfcnt)-1))) signal
from (select a.payout_item_code,a.rowid gid,
a.formula_det||'}' formula_det,
length(a.formula_det) -
length(replace(a.formula_det,'')) + 1 selfcnt,
sum(length(a.formula_det) - length(replace(a.formula_det,''))+1) over(order by rowid) allcnt,sum(length(a.formula_det) - length(replace(a.formula_det,''))+1) over() sumcnt
from a_test a) t1
start with (allcnt-selfcnt)=0 connect by rownum < sumcnt+1 and instr(formula_det,rownum-(allcnt-selfcnt)) >0) a
left join b_test b on instr(a.signal||'}','{'||b.element_id||'}',1)>0
start with a.rn = 1 connect by prior a.gid = a.gid and prior a.rn + 1 = a.rn)
group by gid,formula_det

• 作者: 三十而立
• 时间：2009年10月21日 17:09:43
• 请尊重原创作品。转载请保持文章完整性,并以超链接形式注明原始作者“inthirties（三十而立）”和出处”http://blog.csdn.net/inthirties/archive/2009/10/21/4706281.aspx”,深入讨论可以联系inthirties@gmail.com。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!