17,078
社区成员
发帖
与我相关
我的任务
分享
--手上没有oracle环境,随便写了下。
with t as (
select 1 id, '100,101,' code, 50 price from dual union
select 2, '102,103,', 20 from dual union
select 3, '104,', 30 from dual
)
select t.id,regexp_substr(code,'[^,]+',1,l)
from t,(select level l from dual connect by level < 10) r --这里的10表示最多有10-1=9个逗号分隔。如果不过自行改正。
where length(code)-length(replace(code,',',''))+1>r.l
order by t.id,1;
with t as (
select 1 id, '100,101,' code, 50 price from dual union
select 2, '102,103,', 20 from dual union
select 3, '104,', 30 from dual
)
select t.id,rtrim(regexp_substr(code,'[0-9]+,',1,rn),',') code2
from t,(select rownum rn from dual connect by level < 100) r
where regexp_substr(code,'[0-9]+,',1,rn) is not null
order by t.id,code2
-- 其中level < 100这里的100是取你code字段的最大分割次数,如果超出100你可以把这个值上调。