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转换成多行,要替换的记录有很多条
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转换成多行,要替换的记录有很多条
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(replace(substr(route,1,4),',',''),'123',replace(route,'123','321'),route) route
from t1 id route
--------------------------------
1 1 321,236,569
2 2 321
3 3 1234,569
------------
321
这样可以满足替换
我原帖'有什么简便的方法,最好不要先将字段route转换成多行,要替换的记录有很多条'
之前有想过用regexp_substr,但在存储过程里还要UPDATE,一条记录为1个ID,记录较多,ROUTE字段再分行,存储过程里不太好写,故我问有简便的方法么
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
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
(
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
返回将(所有出现的)from_str中的每个字符替换为to_str中的相应字符以后的string。
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
( 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;
这个正确 结贴
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还是不满足需求的
这个正确 结贴
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还是不满足需求的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了
这个正确 结贴
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还是不满足需求的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了
谢谢额 我用正则也实现了 不过你的方法比较简单
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