Assuming your table structre is like below
UserId AssignedRoles
1 1,12,3,4,5,6
2 6,3,23,1,4
5 2,11,19,12,34
DECLARE @TABLE TABLE (UserId TINYINT, AssignedValues VARCHAR(500))
INSERT INTO @TABLE VALUES (1, '1,12,3,4,5,6')
,(2, '6,3,23,1,4' )
,(3, '2,11,19,12,34')
DECLARE @UserId INT = 3, @RoleId INT = 19
SELECT *
FROM @Table t
WHERE UserId = @UserId
AND @RoleId IN (SELECT Value
FROM dbo.Split(AssignedValues, ',')
)
Here dbo.Split is the normal split function. If you dont have it in your db it is a google search away.