SQL ServerはXMLを利用して文字列の同じ部分を探します
1586 ワード
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行に影響)