主頁 > 知識庫 > oracle 多個字符替換實現

oracle 多個字符替換實現
熱門標簽：網站排名優(yōu)化 鐵路電話系統 AI電銷 地方門戶網站 百度競價排名 服務外包 呼叫中心市場需求 Linux服務器
復制代碼 代碼如下:

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做就比較難度了
復制代碼 代碼如下:

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, payout_item_code, formula_det, row_number() over(partition by gid order by level) rn,
substr(formula_det, decode(rownum-(allcnt-selfcnt), 1, 1, instr(formula_det, '}', 1, rownum-(allcnt-selfcnt)-1)+1), instr(formula_det, '}', 1, rownum-(allcnt-selfcnt))-decode(rownum-(allcnt-selfcnt), 1, 0, instr(formula_det, '}', 1, 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, '}', 1, rownum-(allcnt-selfcnt)) >0) a
left join b_test b on instr(a.signal||'}', '{'||b.element_id||'}', 1, 1)>0
start with a.rn = 1 connect by prior a.gid = a.gid and prior a.rn + 1 = a.rn)
group by gid, payout_item_code, formula_det

  • 作者: 三十而立
  • 時間：2009年10月21日 17:09:43
  • 請尊重原創(chuàng)作品。轉載請保持文章完整性,并以超鏈接形式注明原始作者“inthirties（三十而立）”和出處”http://blog.csdn.net/inthirties/archive/2009/10/21/4706281.aspx”,深入討論可以聯系inthirties@gmail.com。
您可能感興趣的文章:
  • oracle查看字符集和修改字符集使用詳解
  • oracle查詢字符集語句分享
  • 深入分析C#連接Oracle數據庫的連接字符串詳解
  • Oracle中字符串連接的實現方法
  • ORACLE常用數值函數、轉換函數、字符串函數
  • Oracle 多行記錄合并/連接/聚合字符串的幾種方法
  • Oracle 存儲過程總結 二、字符串處理相關函數
  • 45個非常有用的 Oracle 查詢語句小結
  • oracle表空間表分區(qū)詳解及oracle表分區(qū)查詢使用方法
  • oracle數據庫常用的99條查詢語句
  • oracle正則表達式regexp_like的用法詳解
  • oracle查詢不含括號及不含指定字符的方法

標簽：黃山 銅川 蘭州 崇左 仙桃 湘潭 衡水 湖南

巨人網絡通訊聲明：本文標題《oracle 多個字符替換實現》，本文關鍵詞  ；如發(fā)現本文內容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。
  • 相關文章
  • 收縮
    • 微信客服
    • 微信二維碼

    • 電話咨詢

    • 400-1100-266