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, 'bob1@bob.com, bob2@bob.com' email INTO #Temp UNION ALL
SELECT 'b', 'john@john.com' UNION ALL
SELECT 'c', 'pete@pete.com, andrew@andrew.com, will@will.com'

SELECT * FROM #temp

Is it possible to select a row per email address?

i.e.

a bob1@bob.com
a bob2@bob.com
b john@john.com
c pete@pete.com
c andrew@andrew.com
c will@will.com

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