Sql In condition with comma separator

Question

Hi,
create table #temp (ID INT)
INSERT INTO #temp(ID) VALUES (1)

create table #tempTable (ID INT,Menus VARCHAR(10))
INSERT INTO #tempTable(ID,Menus) VALUES (1001,'1'),(1002,'1,2'),(1003,'2,3'),(1004,'1,4'),(1005,'11,4')

select * from #tempTable where (Menus) LIKE (SELECT CONVERT(VARCHAR(20),ID) FROM #temp)

drop table #temp,#tempTable

I need result like
1001
1002
1004

Answer 1

Does this work?

SELECT ID
FROM (
    SELECT ID
        ,Menus
        ,value
    FROM #tempTable
    CROSS APPLY STRING_SPLIT(Menus, ',')
    ) T
WHERE value = 1

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Hi @Mohammad Farook ,

Welcome to Microsoft Q&A!

What is the version of your SQL Server?

If it is SQL Server 2016 and later, please refer below:

 SELECT id   
 FROM #tempTable    
 CROSS APPLY STRING_SPLIT(Menus, ',')  
 WHERE VALUE IN (SELECT ID FROM #temp)  

Output:

ID  
1001  
1002  
1004  

If it is SQL Server 2014 and earlier, please refer below:

Create one function, refer to this forum.

CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );  

Then call this function as below:

 SELECT id  
 FROM #tempTable    
 CROSS APPLY dbo.SplitString(Menus, ',')  
 WHERE VALUE IN (SELECT ID FROM #temp)  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Storing comma-separated lists in table columns is very rarely the right thing to do.

Relational databases are designed from the idea of one value per cell. If you work against that principle, you will be duly punished by having to write complex queries with poor performance.

So do as Viorel suggest and redesign the table.