一区二区三区在线-一区二区三区亚洲视频-一区二区三区亚洲-一区二区三区午夜-一区二区三区四区在线视频-一区二区三区四区在线免费观看

服務器之家：專注于服務器技術及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數據庫技術|

服務器之家 - 數據庫 - Oracle - oracle 多個字符替換實現

oracle 多個字符替換實現

2019-11-08 15:36oracle教程網 Oracle

CSDN上的一個網友，需要一個sql語句的解決方案需求是這樣的求寫oracle多個字符替換（有測試數據）

代碼如下:


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

  • 請尊重原創作品。轉載請保持文章完整性,并以超鏈接形式注明原始作者“inthirties（三十而立）”和出處”http://blog.csdn.net/inthirties/archive/2009/10/21/4706281.aspx”,深入討論可以聯系[email protected]。

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板： 五月婷婷丁香在线视频 | 337p大尺度啪啪人体午夜2020 | 91丝袜足控免费网站xx | 久久久无码精品亚洲欧美 | 韩国甜性涩爱在线播放 | 色综合久久天天综合 | 免费在线观看亚洲 | 国产视频二 | 黑人巨茎大战欧美白妇 | 日韩欧美一区二区三区免费看 | 国产chinese男男gaygay | 全程粗语对白视频videos | 美国雪白人妖sarina | 牛牛影院成人免费网页 | 欧美高清在线精品一区 | 羞羞视频污 | avtt手机版 | 四虎影视国产精品婷婷 | 干妞网免费视频 | 国产第一自拍 | 日韩香蕉网 | 第四色男人天堂 | 99热久久这里只精品国产www | 精品国产91久久久久久久 | 日韩精品首页 | 亚洲欧美国产自拍 | 俄罗斯引擎首页进入 | 99爱视频 | 亚洲欧美韩国日产综合在线 | 狠狠色婷婷狠狠狠亚洲综合 | 亚洲精品无码不卡在线观看 | 亚洲成人精品久久 | 7777色鬼xxxx欧美色夫 | 四虎成人免费观看在线网址 | 亚洲好视频 | 学校捏奶揉下面污文h | 免费国产影视观看网站入口 | 被强迫调教的高辣小说 | 久久伊人影视 | 7788理论片在线观看 | 好吊色视频988gao在线观看 |