寻找差异SQL之间只有2个字符串。 [英] Finding the differences only between 2 strings in SQL

问题描述

我必须比较2 XML字符串（VARCHAR（最大）在SQL）像这样的：

I must compare 2 xml strings(varchar(max) in sql) like these ones:

'<table_name id="2" name="Bob"  job="student"/>'

'<table_name id="2" name="john" job="teacher"/>'

和结果必须是唯一的差异，这意味着，病人获得2串，第一个将我的原始值，而第二个将是新的值。但是，如果不存在差异则会有一个空字符串，在此情况下，例如：

And the result must be the differences only, which means, ill get 2 strings, the first one will me the original values, and the second one will be the new values. However if there are no differences then there will be an empty string, in this case for example:

输出1：NAME =鲍勃，工作=学生

output 1: 'name="Bob", job="student"'

输出2：NAME =约翰，工作=老师

output 2: 'name="john", job="teacher"'

如你所见，我们并未得到ID字符串连接，因为没有任何变化，所以很自然，没有改变意味着没有字符串返回。

As you see, we didn't get the id string attached because there wasn't any change, so naturally, no changes means no string to return.

推荐答案

下面是一个通用的方法达到100 Attributs：

Here is a generic approach up to 100 Attributs:

DECLARE @XML1 XML='<table_name id="2" name="Bob"  job="student"/>';
DECLARE @XML2 XML='<table_name id="2" name="john" job="teacher"/>';

WITH CountAttributs AS
(
    SELECT LEN(CAST(@XML1 AS VARCHAR(MAX)))-LEN(REPLACE(CAST(@XML1 AS VARCHAR(MAX)),'=','')) AS X
)
, E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)) --10 ^ 1
, E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b) -- 10 ^ 2 = 100 rows
, CteTally AS
(
    SELECT TOP((SELECT X FROM CountAttributs)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Nmbr
    FROM E2
)
,AttributNames AS
(
    SELECT Nmbr,@XML1.value('local-name((*/@*[sql:column("Nmbr")])[1])', 'varchar(max)') AS AttributName
    FROM CteTally
)
SELECT
(
    SELECT '' + CASE WHEN @XML1.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)')
                          <> @XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') 
                     THEN AttributName + ': ' + @XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') + ', ' ELSE '' END 
    FROM AttributNames
    FOR XML PATH('') 
)

结果：

name: john, job: teacher, 

这篇关于寻找差异SQL之间只有2个字符串。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！