oracle 多个字符替换实现

时间：2021-05-02

复制代码 代码如下:


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
测试数据如下

复制代码 代码如下:


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, FORMULA_DET)
values ('30102', '({30015}+{30016})*5000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*2500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*2300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*1150');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30104', '({30015}+{30016})*300*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*2300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*5000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*3000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*1500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30006}+{30061}+{30008}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*3800*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({30030}+{30031}+{30032})*38000+{30033}*23000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({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 ('30228', '({30015}+{30016})*450');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30216', '({30015}+{30016})*1300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30216', '({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, FORMULA_DET)
values ('30302', '{30052}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30053}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30054}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30055}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30056}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*4000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*3800');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*100*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*500*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30060}*0');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}/{30057}*150000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*6000');


复制代码 代码如下:


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做就比较难度了

复制代码 代码如下:

声明：本页内容来源网络，仅供用户参考；我单位不保证亦不表示资料全面及准确无误，也不保证亦不表示这些资料为最新信息，如因任何原因，本网内容或者用户因倚赖本网内容造成任何损失或损害，我单位将不会负任何法律责任。如涉及版权问题，请提交至online#300.cn邮箱联系删除。

相关文章