如何在Oracle SQL中对比两列文本并返回差异内容存入新列
实现方案
实现思路
- 第一步:分别将
Col1、Col2的字符串按空格拆分为独立单词的行数据 - 第二步:筛选出
Col2拆分后、不在Col1单词集合中的单词 - 第三步:将筛选得到的差异单词按原始顺序拼接为完整字符串存入
Col3
查询语句实现
可先执行以下查询验证结果是否符合预期:
WITH -- 拆分Col1的所有单词 col1_words AS ( SELECT t.rowid AS row_id, REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) AS word FROM your_table t CONNECT BY REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) IS NOT NULL AND PRIOR t.rowid = t.rowid AND PRIOR SYS_GUID() IS NOT NULL ), -- 拆分Col2的所有单词,同时保留单词原始顺序 col2_words AS ( SELECT t.rowid AS row_id, REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) AS word, LEVEL AS word_order FROM your_table t CONNECT BY REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) IS NOT NULL AND PRIOR t.rowid = t.rowid AND PRIOR SYS_GUID() IS NOT NULL ) -- 筛选差异单词并按空格拼接 SELECT c2.row_id, LISTAGG(c2.word, ' ') WITHIN GROUP (ORDER BY c2.word_order) AS Col3 FROM col2_words c2 LEFT JOIN col1_words c1 ON c2.row_id = c1.row_id AND c2.word = c1.word WHERE c1.word IS NULL GROUP BY c2.row_id;
表数据更新实现
新增
Col3列后,可执行以下语句直接更新表数据:
UPDATE your_table t SET t.Col3 = ( WITH col1_words AS ( SELECT REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) AS word FROM DUAL CONNECT BY REGEXP_SUBSTR(t.Col1, '[^ ]+', 1, LEVEL) IS NOT NULL ), col2_words AS ( SELECT REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) AS word, LEVEL AS word_order FROM DUAL CONNECT BY REGEXP_SUBSTR(t.Col2, '[^ ]+', 1, LEVEL) IS NOT NULL ) SELECT LISTAGG(c2.word, ' ') WITHIN GROUP (ORDER BY c2.word_order) FROM col2_words c2 LEFT JOIN col1_words c1 ON c2.word = c1.word WHERE c1.word IS NULL );
注意事项
- 代码中的
your_table需要替换为实际的表名 - 该方案默认单词以单个空格分隔,如果存在多空格、制表符等空白分隔场景,可将正则匹配规则
[^ ]+修改为[^[:space:]]+兼容 - 如果使用Oracle版本低于12cR2,
LISTAGG拼接结果超过4000字符会报错,可改用XMLAGG实现长字符串拼接
内容的提问来源于stack exchange,提问作者Raja
相关产品推荐
相关产品推荐

