Comma separated value inside IN clause SQL

Question

Create table #temp (name varchar(10) , email varchar(5000))
Insert into #temp values ('sam', '[email protected],[email protected],[email protected]')
Select * from #temp

Create table #temp2(id int, email2 varchar(5000))
Insert into #temp2 values (1, '[email protected]')
select * from #temp2

But when I try to select from below query then its not working.

Select * from #temp2 Where email2 in (Select email from #temp)

I want to pass email in the below format then it will work. '[email protected]','[email protected]','[email protected]' IS there any easier way to achieve this?

Why are you storing delete data at all is the real question here. Don't. Fix the design. — Thom A, Apr 07 '20 at 21:31
Please pick a duplicate from any of the results of searching for `[tsql] in clause comma delimited list`. — HABO, Apr 07 '20 at 22:21

score 1 · Answer 1 · answered Apr 07 '20 at 21:30

One option is:

select t2.* 
from #temp2 t2
where exists (
    select 1 from #temp t where ',' + t.email + ',' like ',%' + t2.email + '%,'
)

Note, however, that this is a rather inefficient approach. Storing delimited lists is a typical anti-pattern in relational databases. I would recommend storing in each email in a separate row rather than in a delimited list. With this set-up at hand, the query that you attempted would work just fine.

Recommended reading: Is storing a delimited list in a database column really that bad? (this is a MySQL question but most observations do apply to any database).

Comma separated value inside IN clause SQL

1 Answers1