Oracle中如何从一个逗号分隔字符串中移除另一逗号分隔字符串内容

实现思路 #

• 第一步：将两个字段的逗号分隔字符串分别拆分为单个元素的行级数据集
• 第二步：对Column2拆分后的结果去重，只要元素出现过就需要被排除
• 第三步：用MINUS关键字计算两个数据集的差集
• 第四步：将差集结果重新拼接为逗号分隔字符串

完整实现代码 #

以下是基于测试场景的可直接运行的示例：

WITH test_data AS (
    -- 测试数据，对应需求给出的场景
    SELECT 'A,B,C,D,E,F' AS Column1,
           'C,D,A,F,C,B' AS Column2
    FROM dual
),
-- 拆分Column1为单行单个元素的结构
split_col1 AS (
    SELECT TRIM(REGEXP_SUBSTR(Column1, '[^,]+', 1, LEVEL)) AS element
    FROM test_data
    CONNECT BY LEVEL <= REGEXP_COUNT(Column1, ',') + 1
),
-- 拆分Column2并去重
split_col2 AS (
    SELECT DISTINCT TRIM(REGEXP_SUBSTR(Column2, '[^,]+', 1, LEVEL)) AS element
    FROM test_data
    CONNECT BY LEVEL <= REGEXP_COUNT(Column2, ',') + 1
),
-- 计算差集
difference AS (
    SELECT element FROM split_col1
    MINUS
    SELECT element FROM split_col2
)
-- 拼接差集为逗号分隔字符串返回
SELECT LISTAGG(element, ',') WITHIN GROUP (ORDER BY element) AS result
FROM difference;

运行上述代码返回的结果为E，和需求预期一致。

注意事项 #

• 如果使用的是Oracle 11g及更早版本，没有内置LISTAGG函数，可以用未公开的WM_CONCAT函数替代，也可以自行实现字符串聚合函数
• 如果需要处理表中多行数据的批量计算，拆分字符串时需要添加主键关联和防循环条件，避免出现笛卡尔集错误，示例如下（假设表名为business_table，主键字段为id）：

split_col1 AS (
    SELECT id, TRIM(REGEXP_SUBSTR(Column1, '[^,]+', 1, LEVEL)) AS element
    FROM business_table
    CONNECT BY LEVEL <= REGEXP_COUNT(Column1, ',') + 1
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL
)

后续计算差集和拼接时按id分组即可得到每行对应的差集结果。

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