Oracle数据库列字符串逗号分隔值拼接对应后缀的格式转换方法

实现逻辑 #

• 第一步：拆分原列中用分号;分隔的每个独立片段
• 第二步：对每个片段拆分出冒号:前的多个逗号分隔前缀、冒号后的统一后缀
• 第三步：拆分每个逗号分隔的前缀，和后缀拼接成前缀:后缀的格式
• 第四步：将所有拼接好的片段用分号;聚合，得到最终结果

完整实现SQL #

WITH test_data AS (
    -- 测试样例，对应给出的三个示例值
    SELECT 'abc,mno:EMP' AS col1 FROM DUAL
    UNION ALL
    SELECT 'xyz:EMP;tyu,opr:PROF' AS col1 FROM DUAL
    UNION ALL
    SELECT 'abc,mno:EMP;tyu,opr:PROF' AS col1 FROM DUAL
),
-- 拆分分号分隔的片段
split_semicolon AS (
    SELECT 
        col1,
        LEVEL AS semicolon_level,
        REGEXP_SUBSTR(col1, '[^;]+', 1, LEVEL) AS semicolon_part
    FROM test_data
    CONNECT BY REGEXP_SUBSTR(col1, '[^;]+', 1, LEVEL) IS NOT NULL
        AND PRIOR col1 = col1
        AND PRIOR SYS_GUID() IS NOT NULL
),
-- 拆分每个片段的前缀部分和后缀
split_colon AS (
    SELECT
        col1,
        semicolon_level,
        REGEXP_SUBSTR(semicolon_part, '^[^:]+') AS prefix_str,
        REGEXP_SUBSTR(semicolon_part, '[^:]+$') AS suffix_str
    FROM split_semicolon
),
-- 拆分逗号分隔的单个前缀，拼接后缀
split_comma AS (
    SELECT
        col1,
        semicolon_level,
        REGEXP_SUBSTR(prefix_str, '[^,]+', 1, LEVEL) || ':' || suffix_str AS single_part
    FROM split_colon
    CONNECT BY REGEXP_SUBSTR(prefix_str, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR col1 = col1
        AND PRIOR semicolon_level = semicolon_level
        AND PRIOR SYS_GUID() IS NOT NULL
)
-- 聚合得到最终结果
SELECT 
    col1 AS original_col,
    LISTAGG(single_part, ';') WITHIN GROUP (ORDER BY semicolon_level, single_part) AS converted_col
FROM split_comma
GROUP BY col1;

输出结果验证 #

处理结果完全匹配需求：

输入abc,mno:EMP → 输出abc:EMP;mno:EMP
输入xyz:EMP;tyu,opr:PROF → 输出xyz:EMP;tyu:PROF;opr:PROF
输入abc,mno:EMP;tyu,opr:PROF → 输出abc:EMP;mno:EMP;tyu:PROF;opr:PROF

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