First of all, I would recommend not to use that csv field, but create a new table to store user-role relations.
But if you are going to keep your current design, you will probably need to use a function to check the csv values. Here’s an example, but I’m not sure what exactly you want to check, so, you might need to make some changes. This function returns 1 if the value sent in @p_value exists in the csv list sent in @p_csv.
At least this should give you an idea.
CREATE FUNCTION YourCheckFunction (@p_csv varchar(100),@p_value varchar(10))
DECLARE @pos integer = 1
SET @pos = CHARINDEX(','+@p_value+',',@p_csv,1)
IF @pos <= 0
SET @pos = CHARINDEX(@p_value+',',@p_csv,1)
IF @pos <> 1
SET @pos = CHARINDEX(','+@p_value,@p_csv,1)
IF @pos + LEN(@p_value) <> LEN(@p_csv)
Carlos is correct, you need to redesign your table. Storing values in a CSV string like this is very poor database design and should be corrected.
You should create a table called UserRole which has two columns UserId and RoleId. For each role that the user has you have one row in this table.