34,837
社区成员




DECLARE @str NVARCHAR(MAX)
DECLARE @xml XML
SET @str = '123,456'
SET @xml = '<root><row>'+REPLACE(@str,',','</row><row>')+'</row></root>'
;WITH tbl(Strs)AS (
SELECT '123' UNION ALL
SELECT '456' UNION ALL
SELECT '123,456' UNION ALL
SELECT '123,700' UNION ALL
SELECT '456,700' UNION ALL
SELECT '123,456,800' UNION ALL
SELECT '900,99'
),
tb2 AS (
SELECT
T.c.value('.','nvarchar(max)') AS nValue
FROM @xml.nodes('/root/row') T(c)
)
SELECT DISTINCT
a.Strs
FROM
tbl a INNER JOIN tb2 b
ON
CHARINDEX(b.nValue+',',a.Strs+',')>0 AND
REPLACE(a.Strs,b.nValue,'')<>'' AND
REPLACE(a.Strs,@str,'')<>''
WITH
/* 测试数据
table1(id,Strs) AS (
SELECT 1,'123' UNION ALL
SELECT 2,'456' UNION ALL
SELECT 3,'123,456' UNION ALL
SELECT 4,'123,700' UNION ALL
SELECT 5,'456,700' UNION ALL
SELECT 6,'123,456,800' UNION ALL
SELECT 7,'900,99'
), */
a AS (
SELECT id,
','+Strs+',' Strs
FROM table1
)
,b AS (
SELECT id,
REPLACE(REPLACE(Strs,
',123,',
','),
',456,',
',') Strs
FROM a
WHERE Strs LIKE '%,123,%' OR
Strs LIKE '%,456,%'
)
SELECT id
FROM b
WHERE LEN(REPLACE(Strs,',',''))<>0
id
-----------
4
5
6