SQL:oracle 数据库 拆分各种符号字符串数据

示例数据: 1,2,2,3,4,5,6,7,8,9; 各种符号都可以

SELECT nvl(REGEXP_SUBSTR(‘1,2,2,3,4,5,6,7,8,9’, ‘[^,]+’, 1, LEVEL, ‘i’),‘NULL’ ) AS str
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(‘1,2,2,3,4,5,6,7,8,9’, ‘,’) + 1

拆分后如下图

可以和LOOP配合使用，如下代码

FOR STR IN (SELECT nvl(REGEXP_SUBSTR(V_FORM_CODE_STR,
‘[^,]+’,
1,
LEVEL,
‘i’),
‘NULL’) AS str
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(V_FORM_CODE_STR, ‘,’) + 1) LOOP
null;
END LOOP;

记录技术人生