Saturday, 1 October 2016

Compare Id from a Comma separated value in Sql Server

I have a column in which UserIds are stored in a comma separated form (as shown below). Now I have a problem that I need to check whether the current user is available in that list or not. 

I was using split function for this but that was taking too much time. As we need to perform this action on millions of records and table is having 20 other columns which we need to returns. 

Id     UserId 
--  ---------------
1    10,11,12,13    
2    10,13,15,4 

I was using split function for this but that was taking too much time. As we need to perform this action on millions of records and table is having 20 other columns which we need to returns.

Then I found the below solution of my problem. 

SELECT * FROM dbo.History 
WHERE (','+ RTRIM(ShareWith)+',') LIKE '%,'+CAST(@UserId AS VARCHAR)+',%')



No comments: