Sean Lange (10/26/2012)

---

Well it looks like Lowell beat me to the punch on this one. I rolled my own version, which also uses the DelimitedSplit8K function. 😉

;with cte as

(

select 1 as ListID, 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' as ToList union all

select 2, 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>Some longer name <LongEmailAddressHere@LongDomainName.com>;Short <e@e.e>' union all

select 3, 'Lowell Mumble Mumble <Lowell@somedomain.com>'

)

, Parse1 as

(

select *

from cte

cross apply dbo.DelimitedSplit8K(ToList, '<')

)

, Parse2 as

(

select p1.ListID, s.Item from parse1 p1

cross apply dbo.DelimitedSplit8K(Item, '>') s

where charindex('@', s.Item) > 0

)

select ListID,

Stuff((select ';' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' ')

from Parse2 p1

group by ListID