oracle字段逗号拆分,Oracle 字段值按逗号拆分，变为多行数据

select distinct * from (

select regexp_substr(q.nums, '[^,]+', 1, Level,'i') order_num, names

from (

select '1,2,3' nums, '张三' names from dual

union all

select '4,5' nums, '李四' names from dual

union all

select '5,6' nums, '王五' names from dual

) q

connect by Level <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1) order by order_num;

1、业务：把nums按逗号拆分为多行。

2、REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr     ：需要进行正则处理的字符串

__pattern    ：进行匹配的正则表达式

__position   ：起始位置，从第几个字符开始正则表达式匹配(默认为1)

__occurrence ：标识第几个匹配组，默认为1

__modifier   ：模式('i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。)