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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/