SQL splitting up email data by comma separated email address

Question

Say I have the following list of names and comma separated email addresses:

SELECT 'a' name, '[email protected], [email protected]' email INTO #Temp UNION ALL
SELECT 'b', '[email protected]' UNION ALL
SELECT 'c', '[email protected], [email protected], [email protected]'

SELECT * FROM #temp

Is it possible to select a row per email address?

i.e.

a [email protected]
a [email protected]
b [email protected]
c [email protected]
c [email protected]
c [email protected]

score 3 · Accepted Answer · answered Oct 15 '13 at 12:45

3

Please try:

SELECT A.name,  
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
FROM  
(
    SELECT name,  
         CAST ('<M>' + REPLACE(email, ',', '</M><M>') + '</M>' AS XML) AS CVS  
    FROM  #Temp 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

answered Oct 15 '13 at 12:45

TechDo

18,398
3
51
64

Thanks, works like a charm...just added a LTRIM(RTRIM()) on adresses – woggles Oct 15 '13 at 12:49
Be careful with `varchar(100)` - e-mail addresses can be 320 characters according to the standard, IIRC. – Aaron Bertrand Oct 15 '13 at 13:26
@AaronBertrand - point of order - the RFC has been clarified by an erratum to state that the max length is 254 characters - see http://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address - but your point still stands – Ed Harper Oct 15 '13 at 13:43

SQL splitting up email data by comma separated email address

1 Answers1