question

MohammadFarook-3243 avatar image
0 Votes"
MohammadFarook-3243 asked MelissaMa-msft commented

Sql In condition with comma separator

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

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe redesign the #tempTable, so that Menus will be an int? The table will have more rows, but the queries will be probably advantageous.



0 Votes 0 ·

Hi @MohammadFarook-3243,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @MohammadFarook-3243,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.