CODEWITHSUNDEEP
sqlsql-server
woggles
woggles

Reputation: 7444

SQL splitting up email data by comma separated email address

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]

Upvotes: 0

Views: 2658

Answers (1)

TechDo
TechDo

Reputation: 18659

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)

Upvotes: 3

Related Questions