Oracle如何拆分多分隔符列并提取拼接后更新同行列？

云原生阿岚

2026-7-6

Oracle拆分字段并拼接更新的解决方案

需求说明

需要处理suppliers_tbl表的Supplier_ID_NAME列：

按逗号拆分每个记录的内容
对每个拆分出的子项，按-分隔后提取第一部分（供应商ID）
将所有提取的ID用逗号拼接，更新到同表的Supplier_id列

输入示例：

ID Supplier_ID_NAME 
1  1234 - Supplier 1,2145 - Supplier 3
2  1235 - Supplier 2
3  1246 - Supplier 4,1234 - Supplier 1, 1478 - Supplier 5

期望输出：

ID Supplier_id
1  1234,2145
2  1235
3  1246,1234,1478

正确实现方式

1. 查询验证结果

先通过查询确认提取拼接后的ID是否正确：

SELECT 
    id,
    LISTAGG(TRIM(REGEXP_SUBSTR(split_item, '^[^-]+')), ',') WITHIN GROUP (ORDER BY rownum) AS Supplier_id
FROM (
    SELECT 
        id,
        TRIM(REGEXP_SUBSTR(Supplier_ID_NAME, '[^,]+', 1, LEVEL)) AS split_item
    FROM suppliers_tbl
    CONNECT BY 
        LEVEL <= REGEXP_COUNT(Supplier_ID_NAME, ',') + 1
        AND PRIOR id = id
        AND PRIOR SYS_GUID() IS NOT NULL -- 防止生成重复行
)
GROUP BY id;

2. 执行更新操作

验证无误后，用MERGE语句更新表：

MERGE INTO suppliers_tbl t
USING (
    SELECT 
        id,
        LISTAGG(TRIM(REGEXP_SUBSTR(split_item, '^[^-]+')), ',') WITHIN GROUP (ORDER BY rownum) AS new_supplier_id
    FROM (
        SELECT 
            id,
            TRIM(REGEXP_SUBSTR(Supplier_ID_NAME, '[^,]+', 1, LEVEL)) AS split_item
        FROM suppliers_tbl
        CONNECT BY 
            LEVEL <= REGEXP_COUNT(Supplier_ID_NAME, ',') + 1
            AND PRIOR id = id
            AND PRIOR SYS_GUID() IS NOT NULL
    )
    GROUP BY id
) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.Supplier_id = s.new_supplier_id;