Yes you can... like this...
DECLARE @S1 VARCHAR(100)
DECLARE @S2 VARCHAR(100)
SET @S1 = '5'
SET @S2 = '1,2,3,4,55,6'
SELECT PATINDEX('%'+@S1+'%',@S2)
But, you notice that in this case, the answer is incorrect. There is no "5" in @S2... the number is incorrectly being returned because of the "55" the "5" picks up on.
If you're not going to split the data, then you must wrap the delimiters in the search on both @S1 and @S2... like this...
DECLARE @S1 VARCHAR(100)
DECLARE @S2 VARCHAR(100)
SET @S1 = '5'
SET @S2 = '1,2,3,4,55,6'
SELECT PATINDEX('%,'+@S1+',%' , ','+@S2+',')
--Jeff Moden
Change is inevitable... Change for the better is not.