ORACLE正则函数(regexp_substr):
根据指定字符将oracle行转列处理,如下列脚本则按照“,”对字符进行分割
with test as (
select '001' xa,'a,b,c,d' xb from dual
union
select '002' xa ,'a,c,b,e' xb from dual
)
select x.xa,regexp_substr(x.xb,'[^,]+',1,level) xb from test x connect by level <= regexp_count(x.xb,'[^,]+')
and prior x.xb = x.xb
and prior dbms_random.value > 0 ;
查询结果如下:
sql脚本粘贴复制就可以直接使用。