以逗号分隔的字符串替换问题

2013-10-21 01:08:46 624 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

sysrepo 开发者指南（一个基于libyang与支持rpc的数据库） sysrepo 开发者指南（一个基于libyang与支持rpc的数据库）日志记录默认情况下，所有 Sysrepo 日志记录都处于关闭状态。如果启用，日志消息有 3 种输出。它们可以写入stderr、由处理syslog或简单地传递给回调。请务必注意，启用 Sysrepo 日志记录也会修改libyang日志记录设置。错误消息被视为标准 Sysrepo 错误，其他严重性较低的消息完全隐藏。要更改此行为，只需在启用 Sysrepo 日志记录后调整libyang日志记录设置。此外，几乎每个 Sysrepo 阅读详情

id route
1 '123,236,569'
2 '123'
3 '1234,569'
如字段route ‘123,236,569’ 以逗号间隔也可能是单个 '123'
现要替换其中的‘123’为'321' ,
如'123,236,569'替换为成 '321,236,569' '123'替换成'321',
但要避免一种情况 '1235,236,569' 不能替换成 '3215,236,569'
如id 1和2 符合条件

有什么简便的方法，最好不要先将字段route转换成多行,要替换的记录有很多条

...全文

大数据应用技术实验报告一 Hadoop三种模式安装配置 JDK+Hadoop安装配置、单机模式配置以下操作在SecureCRT里面完成 1.关闭防火墙 firewall-cmd --state 显示防火墙状态running/not running systemctl stop firewalld 临时关闭防火墙，每次开机重新开启防火墙 systemctl disable firewalld 禁止防火墙服务。 2.传输JDK和HADOOP压缩包 Sec... 阅读详情

specturm

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

Persistence_x 2013-10-30

打赏
举报

引用 23 楼 specturm 的回复:

[quote=引用 22 楼 xiaohuaidan1988 的回复:] [quote=引用 21 楼 specturm 的回复:] [quote=引用 20 楼 xiangagou 的回复:]
WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  )
SELECT id,SUBSTR(REPLACE(route||',','123,','321,'),1,LENGTH(route)) FROM t1;

这个正确结贴[/quote]

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  union all
  SELECT 5 id,'423,44123' FROM dual
  
  )
select substr(REPLACE(route||',','123,','321,'),1,length(route)) from t1

这样的要求最后是44123的这样的就不能替换了吧这个sql还是不满足需求的[/quote]


WITH t1 AS  ( SELECT 1 id,'123,236,569' route FROM dual 
UNION ALL  SELECT 2 id,'123' route FROM dual 
UNION ALL  SELECT 3 id,'1234,569' route FROM dual   
UNION ALL  SELECT 4 id,'423,123' FROM dual   
union all  SELECT 5 id,'423,44123' FROM dual  
) 
select substr(REPLACE(','||route||',',',123,',',321,'),2,length(route)) from t1

再变一下就OK了[/quote] 谢谢额我用正则也实现了不过你的方法比较简单

WITH T1 AS
 (SELECT 1 ID, '123,236,569' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 2 ID, '123' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 3 ID, '1234,569' ROUTE
    FROM DUAL
  UNION ALL
  SELECT 4 ID, '423,123'
    FROM DUAL
  UNION ALL
  SELECT 5 ID, '423,44123' FROM DUAL
  union all
  select 6 ID, '221,123,432' from dual
  )

select regexp_substr(regexp_replace(','||ROUTE||',',',[1]{1}[2]{1}[3]{1},',',321,'),'\d+.*\d') from t1

specturm 2013-10-28

打赏
举报

引用 22 楼 xiaohuaidan1988 的回复:

[quote=引用 21 楼 specturm 的回复:] [quote=引用 20 楼 xiangagou 的回复:]

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  )
SELECT id,SUBSTR(REPLACE(route||',','123,','321,'),1,LENGTH(route)) FROM t1;

这个正确结贴[/quote]

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  union all
  SELECT 5 id,'423,44123' FROM dual
  
  )
select substr(REPLACE(route||',','123,','321,'),1,length(route)) from t1

这样的要求最后是44123的这样的就不能替换了吧这个sql还是不满足需求的[/quote]


WITH t1 AS  ( SELECT 1 id,'123,236,569' route FROM dual 
UNION ALL  SELECT 2 id,'123' route FROM dual 
UNION ALL  SELECT 3 id,'1234,569' route FROM dual   
UNION ALL  SELECT 4 id,'423,123' FROM dual   
union all  SELECT 5 id,'423,44123' FROM dual  
) 
select substr(REPLACE(','||route||',',',123,',',321,'),2,length(route)) from t1

再变一下就OK了

Persistence_x 2013-10-27

打赏
举报

引用 21 楼 specturm 的回复:

[quote=引用 20 楼 xiangagou 的回复:]

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  )
SELECT id,SUBSTR(REPLACE(route||',','123,','321,'),1,LENGTH(route)) FROM t1;

这个正确结贴[/quote]

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  union all
  SELECT 5 id,'423,44123' FROM dual
  
  )
select substr(REPLACE(route||',','123,','321,'),1,length(route)) from t1

这样的要求最后是44123的这样的就不能替换了吧这个sql还是不满足需求的

specturm 2013-10-24

打赏
举报

引用 20 楼 xiangagou 的回复:

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  )
SELECT id,SUBSTR(REPLACE(route||',','123,','321,'),1,LENGTH(route)) FROM t1;

这个正确结贴

qinglang1987 2013-10-23

打赏
举报

select TRANSLATE('123,236,569','123','321') from dual TRANSLATE(string,from_str,to_str) 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。

大明湖畔_帅锅 2013-10-23

打赏
举报

我上面那方法不行么？数据换下也可以将12换成21


with t1 as
(
     select 1 id,'123,236,569' route from dual union all
     select 2 id,'12' route from dual union all
     select 3 id,'1234,569' route from dual  union all
     select 4 id,'12,3' route from dual
)
 
select id,
       decode(substr(route,1,instr(route||',',',')-1),'12',replace(route,'12','21'),route) route
from t1 


     id       route
---------------------------------
1	1	123,236,569
2	2	21
3	3	1234,569
4	4	21,3

善若止水 2013-10-23

打赏
举报

我的想法是这样的，你要先考虑到乱码的种类，先把乱码清除掉，比如把1234替换为*。然后用replace函数把123替换为321，然后把*替换为1234。

空心兜兜 2013-10-23

打赏
举报

持续关注果然现实需求都是很麻烦的

无敌小二傻 2013-10-23

打赏
举报

楼主的这个问题太麻烦了，一直关注中，可到现在没有一个是正确的啊

ccqqwheat 2013-10-23

打赏
举报

CREATE TABLE STUDY(
ID INT PRIMARY KEY,
ROUTE VARCHAR2(255)
);
INSERT INTO STUDY(ID,ROUTE) VALUES(1,'123,236,569');
INSERT INTO STUDY(ID,ROUTE) VALUES(2,'123');
INSERT INTO STUDY(ID,ROUTE) VALUES(3,'1234,569');

SELECT ID,
reverse(SUBSTR(route,0,case when INSTR(route, ',')=0 then LENGTH(route) else INSTR(route, ',')-1 end))
FROM STUDY

大明湖畔_帅锅 2013-10-23

打赏
举报

以第一个逗号为标准来替换


with t1 as
(
     select 1 id,'123,236,569' route from dual union all
     select 2 id,'123' route from dual union all
     select 3 id,'1234,569' route from dual 
)

select id,
       decode(substr(route,1,instr(route||',',',')-1),'123',replace(route,'123','321'),route) route
from t1

specturm 2013-10-23

打赏
举报

引用 6 楼 lyliu602 的回复:

oracle有个函数可以实现反转，楼主可能需要先进行一些处理后，获得需要进行转换的字符，然后用reverse函数就可以了 select reverse('123') col from dual col ------------ 321

可能是‘5897’ 替换 '6897' 我用123 321 举例

specturm 2013-10-23

打赏
举报

引用 7 楼 xiaohuaidan1988 的回复:

希望对你有帮助把字符串分组截取出来

这样可以满足替换我原帖'有什么简便的方法，最好不要先将字段route转换成多行,要替换的记录有很多条' 之前有想过用regexp_substr,但在存储过程里还要UPDATE,一条记录为1个ID,记录较多，ROUTE字段再分行，存储过程里不太好写，故我问有简便的方法么

specturm 2013-10-23

打赏
举报

引用 4 楼 HJ_daxian 的回复:

[quote=引用 3 楼 specturm 的回复:] [quote=引用 1 楼 HJ_daxian 的回复:] 123可能会出现在后面位置吗？

',123' '123,' '123'都可能出现，而且'123'长度不确定, 字符长度可能为4，5，6,7[/quote] 那其他情况的规则呢就是以第一个逗号为标准没有逗号满足就替换全部么？ [/quote] 没其他的规则，就逗号间隔这个规则，也可以是单个'123'

xiangagou 2013-10-23

打赏
举报

WITH t1 AS
  ( SELECT 1 id,'123,236,569' route FROM dual
  UNION ALL
  SELECT 2 id,'123' route FROM dual
  UNION ALL
  SELECT 3 id,'1234,569' route FROM dual
  UNION ALL
  SELECT 4 id,'423,123' FROM dual
  )
SELECT id,SUBSTR(REPLACE(route||',','123,','321,'),1,LENGTH(route)) FROM t1;

大明湖畔_帅锅 2013-10-23

打赏
举报

只判断和替换第一个逗号前的字符串


with t1 as
(
     select 1 id,'123,12,569' route from dual union all
     select 2 id,'12' route from dual union all
     select 3 id,'1234,12' route from dual  union all
     select 4 id,'12,12,123' route from dual
)

select id,
       case when instr(route,',') = 0 then decode(route,'12','21',route)
       when instr(route,',') > 0 then decode(substr(route,1,instr(route,',')-1),'12','21'||substr(route,-(length(route)-length('12'))),route) 
       end route
from t1 

     id       route
----------------------------------
1	1	123,12,569
2	2	21
3	3	1234,12
4	4	21,12,123

specturm 2013-10-23

打赏
举报

引用 11 楼 HJ_daxian 的回复:

以第一个逗号为标准来替换


with t1 as
(
     select 1 id,'123,236,569' route from dual union all
     select 2 id,'123' route from dual union all
     select 3 id,'1234,569' route from dual 
)

select id,
       decode(substr(route,1,instr(route||',',',')-1),'123',replace(route,'123','321'),route) route
from t1

'569,236,1234' 结果 '569,236,3214' 替换了

Persistence_x 2013-10-22

打赏
举报

希望对你有帮助把字符串分组截取出来

无敌小二傻 2013-10-21

打赏
举报

oracle有个函数可以实现反转，楼主可能需要先进行一些处理后，获得需要进行转换的字符，然后用reverse函数就可以了 select reverse('123') col from dual col ------------ 321

u010412956 2013-10-21

打赏
举报

估计要正则，可惜我不是很擅长正则

加载更多回复（4）

以逗号分隔的字符串替换问题

相关推荐