SQL Server利用XML找字符串相同部分
时间：2018-11-20 10:48:01
手机看文章
扫描二维码
随时随地手机看文章
[导读]DECLARE @a NVARCHAR(100)= '01,02,04,05,07';
DECLARE @b NVARCHAR(100)= '01,03,04,05,06';
WITH a1
DECLARE @a NVARCHAR(100)= '01,02,04,05,07';
DECLARE @b NVARCHAR(100)= '01,03,04,05,06';
WITH a1
AS ( SELECT CONVERT(XML, '' + REPLACE(@a, ',', '')
+ '') cxml
),
a AS ( SELECT v.value('.', 'varchar(100)') cv
FROM a1
CROSS APPLY cxml.nodes('/root/v') AS C ( v )
),
b1
AS ( SELECT CONVERT(XML, '' + REPLACE(@b, ',', '')
+ '') cxml
),
b AS ( SELECT v.value('.', 'varchar(100)') cv
FROM b1
CROSS APPLY cxml.nodes('/root/v') AS C ( v )
),
x1
AS ( SELECT 1 AS z ,
a.cv
FROM a
JOIN b ON a.cv = b.cv
),
x0
AS ( SELECT z ,
( SELECT cv + ','
FROM x1
WHERE z = t1.z
ORDER BY z
FOR
XML PATH('')
) AS l
FROM x1 t1
GROUP BY z
)
SELECT LEFT(l, LEN(l) - 1) AS vs
FROM x0;vs
----------------------------------------------------------------
01,04,05
(1 行受影响)
下载文档
