如何在DB2中从多分隔符数据提取对应位置的值？

原始数据表 #

BILL_TYPE                        + BILL_DATE                           + BILL_STATUS
---------------------------------+-------------------------------------+--------------------------------------
EXPECTED]PAYMENT]PAYMENT^PAYMENT + 20230901]20230908]20230915^20230915 + SETTLED]CAPITALISE]CAPITALISE^SETTLED

需求 #

将各列中用]或^分隔的值，按对应位置组成同一行数据，预期结果：

BILL_TYPE        + BILL_DATE + BILL_STATUS
-----------------+-----------+--------------
EXPECTED           20230901    SETTLED
PAYMENT            20230908    CAPITALISE
PAYMENT            20230915    CAPITALISE
PAYMENT            20230915    SETTLED

之前的问题 #

使用单独XMLTABLE拆分每列后，得到类似CROSS JOIN的冗余结果，无法匹配对应位置的值：

XMLTABLE('$doc/items/item'
              PASSING XMLPARSE(DOCUMENT CAST('<items><item><value>'||REPLACE(column_name,']','</value></item><item><value>')||'</value></item></items>' as CLOB)) as "doc"
              new_column_name
              ITEM VARCHAR(255) PATH 'value'
          )

解决方案 #

方法1：带位置索引的XMLTABLE关联 #

通过position()获取拆分值的位置索引，利用索引匹配三列的对应行：

WITH split_data AS (
    SELECT 
        -- 统一替换^为]，生成XML结构
        XMLPARSE(DOCUMENT CAST('<items><item>' || REPLACE(REPLACE(BILL_TYPE, '^', ']'), ']', '</item><item>') || '</item></items>' AS CLOB)) AS bt_xml,
        XMLPARSE(DOCUMENT CAST('<items><item>' || REPLACE(REPLACE(BILL_DATE, '^', ']'), ']', '</item><item>') || '</item></items>' AS CLOB)) AS bd_xml,
        XMLPARSE(DOCUMENT CAST('<items><item>' || REPLACE(REPLACE(BILL_STATUS, '^', ']'), ']', '</item><item>') || '</item></items>' AS CLOB)) AS bs_xml
    FROM your_table
)
SELECT 
    bt.item AS BILL_TYPE,
    bd.item AS BILL_DATE,
    bs.item AS BILL_STATUS
FROM split_data,
     -- 拆分BILL_TYPE并获取索引
     XMLTABLE('$doc/items/item' PASSING bt_xml AS "doc" 
              COLUMNS 
                  idx INT PATH 'position()',
                  item VARCHAR(255) PATH '.'
             ) AS bt,
     -- 通过索引匹配BILL_DATE对应位置的值
     XMLTABLE('$doc/items/item[position() = $idx]' PASSING bd_xml AS "doc", bt.idx AS "idx" 
              COLUMNS 
                  item VARCHAR(255) PATH '.'
             ) AS bd,
     -- 通过索引匹配BILL_STATUS对应位置的值
     XMLTABLE('$doc/items/item[position() = $idx]' PASSING bs_xml AS "doc", bt.idx AS "idx" 
              COLUMNS 
                  item VARCHAR(255) PATH '.'
             ) AS bs;

方法2：使用STRTOK_ARRAY（DB2 11.5+） #

利用STRTOK_ARRAY拆分字符串为数组，结合UNNEST ... WITH ORDINALITY获取索引并关联：

SELECT
    bt.array_val AS BILL_TYPE,
    bd.array_val AS BILL_DATE,
    bs.array_val AS BILL_STATUS
FROM your_table,
     UNNEST(STRTOK_ARRAY(REPLACE(BILL_TYPE, '^', ']'), ']')) WITH ORDINALITY AS bt(array_val, idx),
     UNNEST(STRTOK_ARRAY(REPLACE(BILL_DATE, '^', ']'), ']')) WITH ORDINALITY AS bd(array_val, idx)
WHERE bt.idx = bd.idx
JOIN UNNEST(STRTOK_ARRAY(REPLACE(BILL_STATUS, '^', ']'), ']')) WITH ORDINALITY AS bs(array_val, idx)
ON bt.idx = bs.idx;

核心逻辑 #

1. 统一将^替换为]，确保所有分隔符一致，简化拆分逻辑
2. 为每个拆分值生成位置索引，通过索引匹配三列中同一位置的值，避免交叉连接的冗余结果

内容的提问来源于stack exchange，提问作者athala dwi cahyani