SQL Server - split a column value for multiple row by identified from a delimiter

Question

I have a table which contain two columns(ID, Email) and values as below.

ID    |email
 1    |[email protected];[email protected];[email protected]
 2    |[email protected];[email protected]

The problem is i need to retrieve those values as below by using sql server.

ID  |email
1    [email protected]
1    [email protected]
1    [email protected]
2    [email protected]
2    [email protected]

There are literally 100's of questions on SO on how to do this. If none of the answers in the above duplicate help, I suggest having a search on the site, as you'll find plenty more examples on how to do this. If you try, and fail, then instead post a new question showing your attempt(s), cite the answers you used, and explain why they didn't work. Thanks. — Thom A, May 07 '19 at 10:38

score 2 · Accepted Answer · answered May 07 '19 at 10:39

for sql server 2016 or > 2016

with cte as
(


 select 1 as id,   '[email protected];[email protected];[email protected]' as email
 union all
 select 2    ,'[email protected];[email protected]'
) select id,value from cte CROSS APPLY STRING_SPLIT(email, ';');

id  value
1   [email protected]
1   [email protected]
1   [email protected]
2   [email protected]
2   [email protected]

SQL Server - split a column value for multiple row by identified from a delimiter

1 Answers1