We can achieve this by using XQuery. For this we need to create a function like following..
CREATE FUNCTION [dbo].[Splitter](
@CSVData nvarchar(max),
@SplitOn nvarchar(5)
)
RETURNS @SplitterValues table
(
Data INT
)
AS
BEGIN
DECLARE @Xparam XML;
SELECT @Xparam = CAST('<i>' + REPLACE(@CSVData,@SplitOn,'</i><i>')
+ '</i>' AS XML)
INSERT INTO @SplitterValues SELECT x.i.value('.','INT') FROM
@Xparam.nodes('//i') x(i)
RETURN
END
Now we have to use this in query..
DECLARE @Id INT;
DECLARE @CsvData VARCHAR(MAX)
SET @ID = 41;
SET @CsvData = '41,46,46,41,41,41,41,39,38,202,202,205,205,150,150,150,150,41,41,187'
SELECT 1 WHERE @Id IN
(SELECT Data FROM [dbo].[Splitter](@CsvData,','))
I hope this answer helps you..
Thank you..