CREATE TABLE #tab1
(
id INT ,
aa NVARCHAR(200) ,
bb NVARCHAR(300)
)
INSERT INTO #tab1
( id, aa, bb )
VALUES ( 1, N'aa,bb,cc', -- aa - nvarchar(200)
N'aa,bb,ee,ff' -- bb - nvarchar(300)
) ,
( 2, N'cc', -- aa - nvarchar(200)
N'aa,bb,ee,ff' -- bb - nvarchar(300)
)
SELECT DISTINCT
*
FROM #tab1 t3
INNER JOIN ( SELECT t1.id
FROM #tab1 t1
INNER JOIN ( SELECT B.aa ,
a.id
FROM ( SELECT aa = CONVERT(XML, '<root><v>'
+ REPLACE(aa, ',',
'</v><v>')
+ '</v></root>') ,
id
FROM #tab1 tt
) A
OUTER APPLY ( SELECT
aa = N.v.value('.',
'varchar(100)')
FROM
A.aa.nodes('/root/v') N ( v )
) B
) t2 ON t1.id = t2.id
WHERE t1.bb LIKE '%' + t2.aa + '%'
) t4 ON t3.id = t4.id
(
id INT ,
aa NVARCHAR(200) ,
bb NVARCHAR(300)
)
INSERT INTO #tab1
( id, aa, bb )
VALUES ( 1, N'aa,bb,cc', -- aa - nvarchar(200)
N'aa,bb,ee,ff' -- bb - nvarchar(300)
) ,
( 2, N'cc', -- aa - nvarchar(200)
N'aa,bb,ee,ff' -- bb - nvarchar(300)
)
SELECT DISTINCT
*
FROM #tab1 t3
INNER JOIN ( SELECT t1.id
FROM #tab1 t1
INNER JOIN ( SELECT B.aa ,
a.id
FROM ( SELECT aa = CONVERT(XML, '<root><v>'
+ REPLACE(aa, ',',
'</v><v>')
+ '</v></root>') ,
id
FROM #tab1 tt
) A
OUTER APPLY ( SELECT
aa = N.v.value('.',
'varchar(100)')
FROM
A.aa.nodes('/root/v') N ( v )
) B
) t2 ON t1.id = t2.id
WHERE t1.bb LIKE '%' + t2.aa + '%'
) t4 ON t3.id = t4.id