with t(id,str) as(select 1,'11,12,13,14' from dual union all select 2,'21,22,23' from dual)
方法1，
select id,regexp_substr(str,'[^,]+',1,level) str
from t
connect by level<=regexp_count(str,'[^,]+')
and prior id=id
and prior sys_guid() is not null;
方法2，
select regexp_substr(t.str,'[^,]+',1,v.l)
from t,
(select level l from dual connect by level<=100) v
where v.l<=regexp_count(t.str,'[^,]+');
方法3，
select id,regexp_substr(str,'[^,]+',1,column_value) str
from t,
table(cast(multiset(select level from dual connect by level<=regexp_count(str,'[^,]+')) as sys.odcivarchar2list));
方法1，
select id,regexp_substr(str,'[^,]+',1,level) str
from t
connect by level<=regexp_count(str,'[^,]+')
and prior id=id
and prior sys_guid() is not null;
方法2，
select regexp_substr(t.str,'[^,]+',1,v.l)
from t,
(select level l from dual connect by level<=100) v
where v.l<=regexp_count(t.str,'[^,]+');
方法3，
select id,regexp_substr(str,'[^,]+',1,column_value) str
from t,
table(cast(multiset(select level from dual connect by level<=regexp_count(str,'[^,]+')) as sys.odcivarchar2list));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30363279/viewspace-2127935/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle自定义异常
下一篇：
用model写的一个统计
请登录后发表评论
登录
全部评论
