如何在DB2 SQL中拆分含括号内逗号的列值到多行？

针对你提供的表结构和需求，可通过递归CTE+括号平衡判断的方式，精准识别括号外的逗号作为分隔符，实现正确拆分。假设你的表名为test_table，字段为id和val，具体SQL如下：

WITH recursive_split AS (
    -- 初始数据集：处理原始行，提取第一个有效子串和剩余未处理内容
    SELECT 
        id,
        val AS original_val,
        -- 找到第一个不在括号内的逗号位置
        CASE 
            WHEN LOCATE(',', val) > 0 THEN 
                (SELECT MIN(pos) 
                 FROM (
                     SELECT 
                         POSITION(',', val, i) AS pos,
                         -- 计算当前位置前的括号平衡值：左括号数-右括号数，0表示括号配对完成
                         (LENGTH(SUBSTR(val, 1, POSITION(',', val, i))) - LENGTH(REPLACE(SUBSTR(val, 1, POSITION(',', val, i)), '[', '')))
                         - (LENGTH(SUBSTR(val, 1, POSITION(',', val, i))) - LENGTH(REPLACE(SUBSTR(val, 1, POSITION(',', val, i)), ']', ''))) AS bracket_balance
                     FROM (
                         -- 生成1到字符串长度的序列，逐个检查逗号位置
                         SELECT GENERATE_SERIES(1, LENGTH(val)) AS i
                     ) AS seq
                     WHERE POSITION(',', val, i) > 0
                 ) AS comma_positions
                 WHERE bracket_balance = 0)
            ELSE 0
        END AS split_pos,
        -- 提取第一个拆分后的子串
        CASE 
            WHEN LOCATE(',', val) > 0 THEN TRIM(SUBSTR(val, 1, split_pos - 1))
            ELSE TRIM(val)
        END AS split_val,
        -- 剩余待处理的字符串
        CASE 
            WHEN LOCATE(',', val) > 0 THEN TRIM(SUBSTR(val, split_pos + 1))
            ELSE ''
        END AS remaining_val
    FROM test_table

    UNION ALL

    -- 递归处理剩余字符串，直到无内容可拆分
    SELECT 
        id,
        original_val,
        CASE 
            WHEN LOCATE(',', remaining_val) > 0 THEN 
                (SELECT MIN(pos) 
                 FROM (
                     SELECT 
                         POSITION(',', remaining_val, i) AS pos,
                         (LENGTH(SUBSTR(remaining_val, 1, POSITION(',', remaining_val, i))) - LENGTH(REPLACE(SUBSTR(remaining_val, 1, POSITION(',', remaining_val, i)), '[', '')))
                         - (LENGTH(SUBSTR(remaining_val, 1, POSITION(',', remaining_val, i))) - LENGTH(REPLACE(SUBSTR(remaining_val, 1, POSITION(',', remaining_val, i)), ']', ''))) AS bracket_balance
                     FROM (
                         SELECT GENERATE_SERIES(1, LENGTH(remaining_val)) AS i
                     ) AS seq
                     WHERE POSITION(',', remaining_val, i) > 0
                 ) AS comma_positions
                 WHERE bracket_balance = 0)
            ELSE 0
        END AS split_pos,
        CASE 
            WHEN LOCATE(',', remaining_val) > 0 THEN TRIM(SUBSTR(remaining_val, 1, split_pos - 1))
            ELSE TRIM(remaining_val)
        END AS split_val,
        CASE 
            WHEN LOCATE(',', remaining_val) > 0 THEN TRIM(SUBSTR(remaining_val, split_pos + 1))
            ELSE ''
        END AS remaining_val
    FROM recursive_split
    WHERE remaining_val <> ''
)
-- 输出最终拆分结果
SELECT id, split_val AS val
FROM recursive_split
ORDER BY id, split_val;

逻辑说明 #

1. 括号平衡判断：通过计算每个逗号位置前的左括号与右括号数量差，只有当差值为0时，该逗号才是括号外的有效分隔符
2. 递归拆分：初始行处理原始数据，递归行逐层拆分剩余字符串，直到剩余内容为空
3. 细节处理：用TRIM去掉子串前后的空格，避免拆分后出现多余空格

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