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.

EDIT: Yes I know it breaks normal form, but bigquery for instance has an "unnest" function and has arrays as a datatype.

The suggested duplicate is ancient and SQL Server has better options. — Gordon Linoff, Feb 05 '21 at 14:05

score 3 · Accepted Answer · answered Feb 05 '21 at 13:01

3

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;

answered Feb 05 '21 at 13:01

Gordon Linoff

1,242,037
58
646
786

This works perfectly, I need to read up on cross apply – Agneum Feb 05 '21 at 13:06
@starbyone You also need to read up on First Normal Form – Charlieface Feb 05 '21 at 13:52
1

@Charlieface this is how the data comes in raw format (the user has a single input field to add one or more email-addresses). I do not have access to the backend code – Agneum Feb 08 '21 at 13:05

How do you expand delimited rows in T-SQL

1 Answers1