A SQL Problem. Check condition with in a comma seperated value

I have a vairable

DECLARE @AssignOn nvarchar(20)='0,2,5'

I want to check a condition like开发者_开发问答 this

DECLARE @index int
SET DATEFIRST 7
SELECT @index=DATEPART(DW, GETDATE())-1
IF(CONVERT(nvarchar(2),@index) IN @AssignOn)

IN cannot be used here . Any other methods to do this INLINE

You can use CharIndex to find if you have a match. It returns a non zero value if the first string appears in the second.

IF(CHARINDEX(CONVERT(nvarchar(2),@index), @AssignOn) > 0)

The easiest way to do this is to search for the substring ',needle,' in the csv list string. However, this doesn't work correctly for the first and last elements. This can be overcome by concatenating a comma onto each side of the csv list string.

An example in SQL might be:

SELECT
    CHARINDEX(','+ NEEDLE +',', ','+ HAYSTACK +',')
FROM table;

Or using LIKE:

SELECT *
FROM table
WHERE ','+ HAYSTACK +',' LIKE '%,'+ NEEDLE +',';

IF CHARINDEX(','+CONVERT(nvarchar(2),@index)+',', ','+@AssignOn+',') <> 0

As you actually define the values in the code you could instead;

DECLARE @AssignOn TABLE (value int)
   INSERT @AssignOn VALUES (0),(2),(5)

... @index IN (SELECT value FROM @AssignOn)