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
– Thom A ♦, Commented Apr 7, 2020 at 21:31
Please pick a duplicate from any of the results of searching for [tsql] in clause comma delimited list. — HABO
– HABO, Commented Apr 7, 2020 at 22:21

GMB · Accepted Answer · 2020-04-07 21:30:35Z

1

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).

answered Apr 7, 2020 at 21:30

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comma separated value inside IN clause SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related