tsql - How do you expand delimited rows in T-SQL

Question

I have a table that looks like this:

Emails                                                   | Data
---------------------------------------------------------------------------------
[email protected];[email protected];[email protected]          | Foo
[email protected]                                          | Bar

I want to parse out the delimited emails into their own rows such that it looks something like this:

   Emails                                                   | Data
    ---------------------------------------------------------------------------------
    [email protected]                                          | Foo
    [email protected]                                          | Foo
    [email protected]                                          | Foo
    [email protected]                                          | Bar

I know there is a string_split function, but it would only work on the first column. I need some kind of join for this.

question from:https://stackoverflow.com/questions/66063735/how-do-you-expand-delimited-rows-in-t-sql

Answer 1

In the more recent versions of SQL Server, you can use string_split():

select s.value as email, t.data
from t cross apply
     string_split(t.emails, ';') s;