Oracle中如何将逗号分隔数据转换为指定列的结构化表格

要把你提供的逗号分隔数据转换成带col1、col2、col3列的Oracle表格，同时将空值显示为null，可以用以下几种方法实现：

方法1：使用APEX_STRING（推荐，简洁可靠） #

如果你的Oracle环境支持APEX（通常12c及以上版本默认包含），可以用APEX_STRING.SPLIT_TO_TABLE来快速拆分数据，代码如下：

WITH sample_data AS (
    -- 模拟你的变量数据
    SELECT 'col1,col2,col3' AS column_variable,
           '2017-10-14,abc,1, 2019-10-13,abc,12, 2019-10-12,abc,, ' AS data_clob
    FROM dual
),
split_values AS (
    -- 拆分所有逗号分隔的元素，按行号分组（每3个元素为一行）
    SELECT 
        COLUMN_VALUE AS val,
        CEIL(ROWNUM / 3) AS row_num
    FROM sample_data,
         TABLE(APEX_STRING.SPLIT_TO_TABLE(TRIM(TRAILING ', ' FROM data_clob), ','))
)
-- 将分组后的元素映射到对应列，空字符串转为null
SELECT
    MAX(CASE WHEN MOD(ROWNUM,3)=1 THEN NULLIF(TRIM(val), '') END) AS col1,
    MAX(CASE WHEN MOD(ROWNUM,3)=2 THEN NULLIF(TRIM(val), '') END) AS col2,
    MAX(CASE WHEN MOD(ROWNUM,3)=0 THEN NULLIF(TRIM(val), '') END) AS col3
FROM split_values
GROUP BY row_num
ORDER BY row_num;

执行结果： #

方法2：原生正则表达式（无额外依赖） #

如果你的环境没有APEX，可以用Oracle原生的正则函数来拆分，兼容性更好：

WITH sample_data AS (
    SELECT 'col1,col2,col3' AS column_variable,
           '2017-10-14,abc,1, 2019-10-13,abc,12, 2019-10-12,abc,, ' AS data_clob
    FROM dual
),
split_rows AS (
    -- 先按", "拆分出每行数据
    SELECT 
        TRIM(REGEXP_SUBSTR(data_clob, '(.*?)(, |$)', 1, LEVEL, NULL, 1)) AS row_data
    FROM sample_data
    CONNECT BY LEVEL <= REGEXP_COUNT(data_clob, ', ') + 1
    WHERE TRIM(REGEXP_SUBSTR(data_clob, '(.*?)(, |$)', 1, LEVEL, NULL, 1)) IS NOT NULL
),
split_cols AS (
    -- 再拆分每行的三个列，空值转为null
    SELECT
        NULLIF(TRIM(REGEXP_SUBSTR(row_data, '(.*?)(,|$)', 1, 1, NULL, 1)), '') AS col1,
        NULLIF(TRIM(REGEXP_SUBSTR(row_data, '(.*?)(,|$)', 1, 2, NULL, 1)), '') AS col2,
        NULLIF(TRIM(REGEXP_SUBSTR(row_data, '(.*?)(,|$)', 1, 3, NULL, 1)), '') AS col3
    FROM split_rows
)
SELECT col1, col2, col3 FROM split_cols;

关键细节说明： #

• TRIM(TRAILING ', ' FROM data_clob)：先去掉数据末尾多余的逗号和空格，避免生成空行；
• NULLIF(TRIM(val), '')：把空字符串或纯空格的内容转为null，符合你的需求；
• 正则(.*?)(, |$)：非贪婪匹配，确保正确拆分出每行数据，包括包含空字段的行。

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