mysql| mssql| access| oracle DB2| sqlite| MongoDB| redis| nosql| Postgre SQL| mariadb|

oracle

推荐列表 站点导航

当前位置：首页 > 数据库 > oracle >

oracle 多个字符替换实现

来源:网络整理  作者:网络  发布时间:2020-12-13 01:49
CSDN上的一个网友，需要一个sql语句的解决方案需求是这样的求写oracle多个字符替换（有测试数据）...
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30044', 'a34');
insert into b_test (ELEMENT_ID, NAME)
values ('30102', '({30015}+{30016})*1500');
values ('30043', 'a33');
insert into b_test (ELEMENT_ID, NAME)
values ('30026', 'a16');
values ('30061', 'a51');
values ('30058', 'a48');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*3000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30042', 'a32');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)

复制代码 代码如下:


insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30051', 'a41');
insert into b_test (ELEMENT_ID, NAME)
values ('30226', '{30006}+{30061}+{30008}');
values ('30216', '({30015}+{30016})*1300');
测试数据如下
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30047', 'a37');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30302', '{30051}');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30021', 'a11');
values ('30039', 'a29');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)


insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({30030}+{30031}+{30032})*38000+{30033}*23000');
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30034', 'a24');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30056}');
insert into b_test (ELEMENT_ID, NAME)
但是用sql做就比较难度了
values ('30030', 'a20');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30226', '{30057}*2300');
values ('30229', '({30015}+{30016})*1400');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*6000');
values ('30029', 'a19');
create table B_TEST
values ('30226', '{30057}*4000');
insert into b_test (ELEMENT_ID, NAME)
values ('30017', 'a7');
values ('30302', '{30053}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30053', 'a43');
values ('30038', 'a28');
values ('30008', 'a2');
values ('30226', '{30057}*3800*12');
values ('30023', 'a13');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30015', 'a5');
NAME VARCHAR2(41)
values ('30226', '{30057}/{30057}*150000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30020', 'a10');
insert into b_test (ELEMENT_ID, NAME)


values ('30046', 'a36');

复制代码 代码如下:

values ('30036', 'a26');
values ('30302', '{30052}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*5000');
values ('30057', 'a47');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30010', 'a4');
insert into b_test (ELEMENT_ID, NAME)

values ('30040', 'a30');
values ('30226', '{30057}*3800');
values ('30018', 'a8');
insert into b_test (ELEMENT_ID, NAME)
values ('30216', '({30015}+{30016})*650');
values ('30016', 'a6');
insert into b_test (ELEMENT_ID, NAME)
values ('30027', 'a17');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
这个如果用function或者是sp做，就没有什么难度了。
values ('30031', 'a21');
values ('30060', 'a50');

复制代码 代码如下:

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)

)
values ('30024', 'a14');
values ('30032', 'a22');
insert into b_test (ELEMENT_ID, NAME)

values ('30102', '({30015}+{30016})*5000');

values ('30045', 'a35');
values ('30025', 'a15');
values ('30228', '({30015}+{30016})*450');
values ('30226', '{30060}*0');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30041', 'a31');
insert into b_test (ELEMENT_ID, NAME)
values ('30033', 'a23');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
ELEMENT_ID VARCHAR2(5) not null,
insert into b_test (ELEMENT_ID, NAME)
values ('30006', 'a1');
insert into b_test (ELEMENT_ID, NAME)
values ('30054', 'a44');
values ('30022', 'a12');
values ('30059', 'a49');
values ('30055', 'a45');
values ('30226', '{30057}*100*12');
values ('30037', 'a27');
values ('30102', '({30015}+{30016})*1150');
values ('30035', 'a25');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30104', '({30015}+{30016})*300*12');
FORMULA_DET VARCHAR2(1000)
)
(
values ('30009', 'a3');
values ('30028', 'a18');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30056', 'a46');
values ('30048', 'a38');
insert into b_test (ELEMENT_ID, NAME)
values ('30102', '({30015}+{30016})*2500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30055}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30049', 'a39');
insert into b_test (ELEMENT_ID, NAME)

复制代码 代码如下:

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30307', '({30015}+{30016})*360');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30050', 'a40');
insert into b_test (ELEMENT_ID, NAME)
values ('30210', '({30030}+{30031}+{30032}+{30033})*38000+{30036}*10000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*1500');
values ('30228', '({30015}+{30016})*450');
insert into b_test (ELEMENT_ID, NAME)

values ('30226', '{30057}*500*12');
values ('30102', '({30015}+{30016})*2300');
values ('30302', '{30054}');
PAYOUT_ITEM_CODE VARCHAR2(30) not null,
values ('30019', 'a9');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
(
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30052', 'a42');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)


insert into b_test (ELEMENT_ID, NAME)
FORMULA_DET列里ELEMENT_ID替换成NAME
create table A_TEST
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)

相关热词： oracle

本站内容来源于网络，如有侵权请与我们联系，我们会及时删除，我们深感抱歉！
注：本站所有信息仅供用于网络技术学习参考,学习中请遵循相关法律法规！

本文地址: https://v30.fanwenzhu.com/sql/oracle/3137.shtml

上一篇：Oracle 存储过程教程
下一篇：基于ORA-12170 TNS 连接超时解
相关文章
热门TAG
win10 ecshop 主机 阿里云 解决 配置 C# C++ 解析 SQL语句 命令 Go语言 方法 CSS3 HTML5 CSS win7 MSSQL 服务器配置 IIS7.5 IIS7 IIS6 IIS CentOS 7 Linux oracle数据库 oracle phpcms discuz discuz教程
最新文章
是因为scan的IP问题补占用 是因为scan的IP问题补占用

时间：2021-01-20

还你一个干净的model 2、功 还你一个干净的model 2、功

时间：2021-01-20

既然 ROWNUM 列并不是真实存 既然 ROWNUM 列并不是真实存

时间：2021-01-20

Real Application Clusters Real Application Clusters

时间：2021-01-20

在tools-preferences-connection（ 在tools-preferences-connection（

时间：2021-01-20

Windows Sever 2012下Oracle 12c安 Windows Sever 2012下Oracle 12c安

时间：2021-01-10

Oracle安装监听器错误的解 Oracle安装监听器错误的解

时间：2021-01-07

oracle远程连接服务器数据 oracle远程连接服务器数据

时间：2021-01-07

热门文章
Oracle存储过程编程详解 Oracle存储过程编程详解

时间：2020-12-07

Azure Queue Storage 基本用法 Azure Storage 之 Azure Queue Storage 基本用法 Azure Storage 之

时间：2020-12-26

win10下oracle 11g安装图文教程 win10下oracle 11g安装图文教程

时间：2020-12-25

oracle 数据库学习 基本结构介绍 oracle 数据库学习 基本结构介绍

时间：2020-12-13

Azure File Storage 基本用法 Azure Storage 之 F Azure File Storage 基本用法 Azure Storage 之 F

时间：2020-12-26

windows使用sqlpus连接oracle 数据库的教程图 windows使用sqlpus连接oracle 数据库的教程图

时间：2020-12-25

Window下Oracle Database 11g 发行版2安装教程 Window下Oracle Database 11g 发行版2安装教程

时间：2020-12-29

Oracle解锁的方式介绍 Oracle解锁的方式介绍

时间：2020-12-14

Oracle 12c Study之Installer Oracle Oracle 12c Study之Installer Oracle

时间：2021-01-06

linux下oracle设置开机自启动实现方法 linux下oracle设置开机自启动实现方法

时间：2020-12-13

Copyright © www.juheyunku.com      关于 | 合作 | 声明 | 联系 | 更新 | 地图 | Tags

oracle 多个字符替换实现

2020-12-13　编辑：网络

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30044', 'a34');
insert into b_test (ELEMENT_ID, NAME)
values ('30102', '({30015}+{30016})*1500');
values ('30043', 'a33');
insert into b_test (ELEMENT_ID, NAME)
values ('30026', 'a16');
values ('30061', 'a51');
values ('30058', 'a48');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*3000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30042', 'a32');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)

复制代码 代码如下:


insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30051', 'a41');
insert into b_test (ELEMENT_ID, NAME)
values ('30226', '{30006}+{30061}+{30008}');
values ('30216', '({30015}+{30016})*1300');
测试数据如下
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30047', 'a37');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30302', '{30051}');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30021', 'a11');
values ('30039', 'a29');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)


insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({30030}+{30031}+{30032})*38000+{30033}*23000');
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30034', 'a24');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30056}');
insert into b_test (ELEMENT_ID, NAME)
但是用sql做就比较难度了
values ('30030', 'a20');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30226', '{30057}*2300');
values ('30229', '({30015}+{30016})*1400');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*6000');
values ('30029', 'a19');
create table B_TEST
values ('30226', '{30057}*4000');
insert into b_test (ELEMENT_ID, NAME)
values ('30017', 'a7');
values ('30302', '{30053}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30053', 'a43');
values ('30038', 'a28');
values ('30008', 'a2');
values ('30226', '{30057}*3800*12');
values ('30023', 'a13');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30015', 'a5');
NAME VARCHAR2(41)
values ('30226', '{30057}/{30057}*150000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
values ('30020', 'a10');
insert into b_test (ELEMENT_ID, NAME)


values ('30046', 'a36');

复制代码 代码如下:

values ('30036', 'a26');
values ('30302', '{30052}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*5000');
values ('30057', 'a47');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30010', 'a4');
insert into b_test (ELEMENT_ID, NAME)

values ('30040', 'a30');
values ('30226', '{30057}*3800');
values ('30018', 'a8');
insert into b_test (ELEMENT_ID, NAME)
values ('30216', '({30015}+{30016})*650');
values ('30016', 'a6');
insert into b_test (ELEMENT_ID, NAME)
values ('30027', 'a17');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
这个如果用function或者是sp做，就没有什么难度了。
values ('30031', 'a21');
values ('30060', 'a50');

复制代码 代码如下:

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)

)
values ('30024', 'a14');
values ('30032', 'a22');
insert into b_test (ELEMENT_ID, NAME)

values ('30102', '({30015}+{30016})*5000');

values ('30045', 'a35');
values ('30025', 'a15');
values ('30228', '({30015}+{30016})*450');
values ('30226', '{30060}*0');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30041', 'a31');
insert into b_test (ELEMENT_ID, NAME)
values ('30033', 'a23');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
ELEMENT_ID VARCHAR2(5) not null,
insert into b_test (ELEMENT_ID, NAME)
values ('30006', 'a1');
insert into b_test (ELEMENT_ID, NAME)
values ('30054', 'a44');
values ('30022', 'a12');
values ('30059', 'a49');
values ('30055', 'a45');
values ('30226', '{30057}*100*12');
values ('30037', 'a27');
values ('30102', '({30015}+{30016})*1150');
values ('30035', 'a25');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30104', '({30015}+{30016})*300*12');
FORMULA_DET VARCHAR2(1000)
)
(
values ('30009', 'a3');
values ('30028', 'a18');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30056', 'a46');
values ('30048', 'a38');
insert into b_test (ELEMENT_ID, NAME)
values ('30102', '({30015}+{30016})*2500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30055}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30049', 'a39');
insert into b_test (ELEMENT_ID, NAME)

复制代码 代码如下:

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30307', '({30015}+{30016})*360');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30050', 'a40');
insert into b_test (ELEMENT_ID, NAME)
values ('30210', '({30030}+{30031}+{30032}+{30033})*38000+{30036}*10000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015}+{30016})*1500');
values ('30228', '({30015}+{30016})*450');
insert into b_test (ELEMENT_ID, NAME)

values ('30226', '{30057}*500*12');
values ('30102', '({30015}+{30016})*2300');
values ('30302', '{30054}');
PAYOUT_ITEM_CODE VARCHAR2(30) not null,
values ('30019', 'a9');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
(
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
values ('30052', 'a42');
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)


insert into b_test (ELEMENT_ID, NAME)
FORMULA_DET列里ELEMENT_ID替换成NAME
create table A_TEST
insert into b_test (ELEMENT_ID, NAME)
insert into b_test (ELEMENT_ID, NAME)

本站内容来源于网络，如有侵权请与我们联系，我们会及时删除，我们深感抱歉！
注：本站所有信息仅供学习参考！
本文地址为 https://v30.fanwenzhu.com/sql/oracle/3137.shtml

相关文章

风云图片

是因为scan的IP问题补占用

是因为scan的IP问题补占用

还你一个干净的model 2、功

还你一个干净的model 2、功

既然 ROWNUM 列并不是真实存

既然 ROWNUM 列并不是真实存

Real Application Clusters

Real Application Clusters

在tools-preferences-connection（

在tools-preferences-connection（

Windows Sever 2012下Oracle 12c安

Windows Sever 2012下Oracle 12c安

推荐阅读

返回oracle频道首页