How do I join a comma delimited string?

Question

I want to combine two tables, one table with an email field, and the other a comma separated list.

This is the scenario:

Tables

Team -------------- - team_id - email_list (this is a comma separated email address) Persons -------------- - person_id - email 

I tried something like this:

---

 SELECT team.* FROM team INNER JOIN persons ON trim(persons.email) IN (CONCAT('\'',REPLACE(REPLACE(team.email_list,' ',''),',','\',\''),'\'')) 

but the line inside the IN clause looks like this: "email1", "email2", "email3" "

Any ideas to make it work?

Answer 1

MySQL has a built-in function that can help with comma-separated lists:

 SELECT . . . FROM team t INNER JOIN persons p ON FIND_IN_SET(p.email, t.email_list); 

But you will not be satisfied with the performance, because you can not optimize the use of the index.

See also my answer to Is storing a list with commas in a database column really that bad?

Answer 2

I used it for MSSQL, I personally don’t like it, but for this one report or data task it does the trick.

 declare @team table (team_id int,email_list varchar(8000)); declare @persons table (person_id int,email varchar(64)); insert into @team SELECT 1,' [e-mail] , [e-mail] ' union all SELECT 2,' [e-mail] , [e-mail] ' union all SELECT 3,' [e-mail] , [e-mail] ' union all SELECT 4,' [e-mail] , [e-mail] , [e-mail] ' UNION ALL SELECT 3,' [e-mail] , [e-mail] ' ; insert into @persons select 1,' [e-mail] ' union all select 2,' [e-mail] ' union all select 3,' [e-mail] '; select Team.team_id, Team.email_list, Person.person_id, Person.email 'matched_on' from @team Team INNER JOIN @persons Person on ','+Team.email_list+',' LIKE '%,'+Person.email+',%'; 

Updated by David