分隔字符串的匹配

--数据库有一个字段存的格式为:1-2-4-6,   传一个2-6怎样将它匹配出来
IF OBJECT_ID('dbo.test','U') IS NOT NULL
	DROP TABLE dbo.test
GO
CREATE TABLE dbo.test(
	id INT IDENTITY(1,1),
	code VARCHAR(100)	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.test(code) VALUES('1-2-4-6')
INSERT INTO dbo.test(code) VALUES('2')
INSERT INTO dbo.test(code) VALUES('6')
INSERT INTO dbo.test(code) VALUES('22-26')
--方法一、需要事先拆分搜索串
SELECT * FROM dbo.test WHERE '-'+code+'-' LIKE '%-2-%' AND '-'+code+'-' like '%-6-%' 
/*
id	code
1	1-2-4-6
*/
--方法二、代码中拆分搜索串
DECLARE @codition VARCHAR(MAX)
SET @codition='2-6'
;WITH t2 AS (	
	SELECT B.single FROM (
		SELECT [value] = CONVERT(XML,'<v>' + REPLACE(@codition, '-', '</v><v>')+ '</v>')
	) A
	OUTER APPLY(
		SELECT single = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)
	) B
	WHERE  ISNULL(B.single, '') != ''
)
,t3 AS (
	SELECT t.id,COUNT(t.id) AS cnt FROM dbo.test T CROSS APPLY t2
	WHERE '-'+t.code+'-' like '%-'+t2.single+'-%'
	GROUP BY t.id
)
SELECT * FROM dbo.test AS t4 WHERE EXISTS(
	SELECT * FROM t3 WHERE t3.id=t4.id AND t3.cnt=(SELECT COUNT(1) FROM t2)	
)
/*
id	code
1	1-2-4-6
*/