Splitting delimited strings into rows

[intapiuser]

When would I use this:

In cases when you have a table containing an identifier field with a related field containing delimited text and you need to split into a new row per split subfield.

Example:

The Email Address column contains email addresses delimited by commas. We'd like to transform the upper table into the lower table where each email address is its own row related to its payroll.

Using the sample data from above:

CREATE TABLE Testdata(PayrollId int, Country VARCHAR(2), EmailAddress varchar(max))
INSERT Testdata select 1, 'GB', 'email1,email2,email3'
INSERT Testdata select 2, 'GB', 'email1,email2'

We create a temporary table to hold the results of the recursion:

;WITH tmp(PayrollId, Country, EmailAd, EmailAddress) as

(

  SELECT PayrollId, Country, LEFT(EmailAddress, CHARINDEX(',',EmailAddress+',')-1),
  STUFF(EmailAddress,1,CHARINDEX(',',EmailAddress+','),'')
  FROM Testdata

  UNION ALL

  SELECT PayrollId, Country, LEFT(EmailAddress, CHARINDEX(',',EmailAddress+',')-1),
  STUFF(EmailAddress,1,CHARINDEX(',',EmailAddress+','),'')
  FROM tmp
  WHERE EmailAddress > ''
)

Note: Tested on SQL Server.