17,377
社区成员
发帖
与我相关
我的任务
分享
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
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了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,'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
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
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;
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