SQL实现多行列字符串公共术语提取并按规则用/拼接的问题

需要编写SQL实现两列字符串的公共术语提取拼接，规则为：提取公共内容放至/左侧，剩余非公共内容合并放至/右侧，参考示例如下：

ID ROW1  ROW2  FINAL_RESULT
1   A     A,B      A/B
2   A/B   A,C      A/B,C
3   A,B   A,C      A/B,C

现有写法无法得到预期结果：

update table1
set  FINAL_RESULT = 
 case when ( CHARINDEX( ROW1  ,ROW2 ) > 0 ) then
                    ROW1  + '/' +Replace(ROW2  , ROW1  , '')

现有语句存在3个核心问题：

• 缺少CASE语句的ELSE分支，语法不完整，ROW1未完整出现在ROW2中时会返回空值
• 直接替换后会出现多余的前置/后置逗号，比如ROW2=A,B替换A后会得到,B
• 未处理术语部分匹配的场景，比如ROW1=A,B、ROW2=A,C时，公共术语为A，现有逻辑无法识别这类部分匹配的情况

-- 先执行SELECT验证结果是否符合预期
SELECT 
    ID,
    ROW1,
    ROW2,
    CASE
        -- 处理ROW1完整存在于ROW2的场景
        WHEN CHARINDEX(ROW1, ROW2) > 0 
        THEN ROW1 + '/' + TRIM(',' FROM REPLACE(ROW2, ROW1, ''))
        -- 处理部分术语匹配的场景
        ELSE 
            -- 提取公共术语
            (SELECT STRING_AGG(value, ',') FROM (
                SELECT value FROM STRING_SPLIT(REPLACE(ROW1, '/', ','), ',')
                INTERSECT
                SELECT value FROM STRING_SPLIT(REPLACE(ROW2, '/', ','), ',')
            ) t)
            + '/' +
            -- 合并两边非公共术语
            STUFF((
                SELECT ',' + value FROM (
                    SELECT value FROM STRING_SPLIT(REPLACE(ROW1, '/', ','), ',')
                    WHERE value NOT IN (SELECT value FROM STRING_SPLIT(REPLACE(ROW2, '/', ','), ','))
                    UNION ALL
                    SELECT value FROM STRING_SPLIT(REPLACE(ROW2, '/', ','), ',')
                    WHERE value NOT IN (SELECT value FROM STRING_SPLIT(REPLACE(ROW1, '/', ','), ','))
                ) t FOR XML PATH('')), 1, 1, '')
    END AS CALC_FINAL_RESULT
FROM table1

-- 验证无误后执行UPDATE
-- UPDATE table1
-- SET FINAL_RESULT = [上面CASE逻辑]

不同数据库适配说明 #

• MySQL场景：将CHARINDEX替换为LOCATE，STRING_SPLIT替换为自定义拆分函数或SUBSTRING_INDEX递归实现，STRING_AGG替换为GROUP_CONCAT
• PostgreSQL场景：将STRING_SPLIT替换为STRING_TO_TABLE，FOR XML PATH拼接逻辑替换为STRING_AGG

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