比较2个文本列并使用sql在第三个单元格中显示差异

Tinku 发表于 Dev

石膏

我正在尝试比较2列，例如，我必须得到唯一的区别

select * from table1

Column_1                column_2
----------------        ------------------
Swetha working          Swetha is working in Chennai
Raju 10th               Raju is studying 10th std
ranjith                 Ranjith played yesterday
how to play             how to play Cricket
My name is              my name is john

输出：

如果中间有单词，也应该像第1行和第2行一样删除

Column_1                column_2                         column_3
----------------        ------------------               ------------------------
Swetha working          Swetha is working in Chennai     is in Chennai
Raju 10th               Raju is studying 10th std        is studying std
ranjith                 Ranjith played yesterday         played yesterday
how to play             how to play Cricket              Cricket
My name is              my name is john                  john

戈登·利诺夫

这比您先前的问题要复杂得多。您可以将第一列分解为单词，然后在第二列中分别替换它们。但是，为此，您需要递归CTE：

with words as (
      select t.*, s.*,
             max(s.seqnum) over (partition by t.id) as max_seqnum
      from t cross apply
           (select s.value as word,
                   row_number() over (order by (select null)) as seqnum
            from string_split(col1, ' ') s
           ) s
     ),
     cte as (
      select id, col1, col2,
             replace(' ' + col2 + ' ', ' ' + word + ' ', ' ') as result,
             word, seqnum, max_seqnum
      from words
      where seqnum = 1
      union all
      select cte.id, cte.col1, cte.col2,
             replace(cte.result, ' ' + w.word + ' ', ' '),
             w.word, w.seqnum, cte.max_seqnum
      from cte join
           words w
           on w.id = cte.id and w.seqnum = cte.seqnum + 1
     )
select id, col1, col2, ltrim(rtrim(result)) as result
from cte
where max_seqnum = seqnum
order by id;

这是db <>小提琴。

我添加了一个，id因此每一行都是唯一定义的。如果您的SQL Server版本不具有内置string_split()功能，则可以轻松找到具有相同功能的版本。

这使用的一个技巧是处理第二列中的第一个和最后一个单词。该代码在开头和结尾添加空格。这样，字符串中的所有单词都被空格包围，从而更容易替换完整的单词。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。