0
Create table #temp (name varchar(10) , email varchar(5000))
Insert into #temp values ('sam', 'sam@email.com,pam@email.com,kam@email.com')
Select * from #temp

Create table #temp2(id int, email2 varchar(5000))
Insert into #temp2 values (1, 'sam@email.com')
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. 'sam@email.com','pam@email.com','kam@email.com' IS there any easier way to achieve this?

GMB
  • 216,147
  • 25
  • 84
  • 135
nick
  • 323
  • 2
  • 14
  • 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

1 Answers1

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

GMB
  • 216,147
  • 25
  • 84
  • 135