Oracle SQL中按顺序拆分CSV字符串为行（含空值）

需求明确：将CSV格式字符串拆分为单独行，空值需严格按原顺序保留为独立行。例如输入,,hello,,,world,,，需输出8行，空值与非空值的顺序完全匹配原始字符串。

原有方案的问题 #

常规使用REGEXP_SUBSTR搭配[^,]+的写法（如下）会跳过空值，导致非空值前置、空值后置，无法保留原始顺序：

WITH
qstr AS (select ',,,,1,2,3,4,,' str from dual)
SELECT level ||'->'||
REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) value FROM qstr
CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1

临时通过replace(',',' ,')给逗号前加空格的方法虽能凑效，但本质是用空格占位模拟空值，不仅不够严谨，还额外增加了字符串替换的性能开销。

高效解决方案 #

方案1：兼容Oracle 11g及以上的正则优化写法

通过调整正则表达式，直接匹配包括空值在内的每个分隔段，避免跳过空值：

WITH qstr AS (
    SELECT ',,,,1,2,3,4,,' AS str FROM dual
)
SELECT 
    level || '->' || REGEXP_SUBSTR(str || ',', '(.*?)(,|$)', 1, LEVEL, NULL, 1) AS value
FROM qstr
CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1

• 原理：给字符串末尾追加逗号，确保最后一个空值也能被匹配；(.*?)(,|$)采用非贪婪匹配，捕获两个逗号（或字符串结尾）之间的所有内容（包括空内容）；NULL,1指定提取第一个捕获组，即每个分隔段的原始值（空值直接返回空）。

方案2：Oracle 12c+ 推荐使用JSON_TABLE（性能更优）

利用Oracle原生的JSON解析能力，将CSV转换为JSON数组后展开，性能比正则递归更高效，尤其适合处理长字符串或大量数据：

WITH qstr AS (
    SELECT ',,,,1,2,3,4,,' AS str FROM dual
)
SELECT 
    rownum || '->' || value AS value
FROM qstr,
     JSON_TABLE(
         '["' || REPLACE(str, ',', '","') || '"]',
         '$[*]' COLUMNS value VARCHAR2(100) PATH '$'
     )

• 原理：通过REPLACE将CSV字符串转换为标准JSON数组格式（如,,,1变为["","","","1"]），再用JSON_TABLE遍历数组元素，每个元素（包括空值）都会被按顺序提取为单独行。

内容的提问来源于stack exchange，提问作者Sannu