well, if your data is split with semicolons or some known string,, then you could split it with the DelimitedSplit8K function.

www.sqlservercentral.com/articles/Tally+Table/72993/

With tblDoc([FROM])

AS

(

SELECT 'John Doe1 <John.Doe1@abcd.com>' UNION ALL

SELECT 'John Doe2 <John.Doe2@abcd.com>' UNION ALL

SELECT 'John Doe3 <John.Doe3@abcd.com>' UNION ALL

SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' UNION ALL

SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>' UNION ALL

SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com> ;Bill Doe1 <Billie.Doe1@abc.com>; Katie Doe1 <Katie.Doe1@abc.com>' UNION ALL

SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org> ;Bill Doe1 <Billie.Doe1@abc.com>; ' UNION ALL --notice teh EXTRA semicolon

SELECT 'Lowell Mumble Mumble <Lowell@somedomain.com>'

),

SplitData

AS

(

SELECT myfunc.Item

FROM tblDoc

CROSS APPLY dbo.DelimitedSplit8K([FROM],';') myfunc

),

tblDoc2

As

(

SELECT

CHARINDEX('<', Item) As LeftBracket,

CHARINDEX('>', Item) As RightBracket,

Item

FROM SplitData

)

SELECT SUBSTRING(Item,LeftBracket +1,RightBracket - (LeftBracket +1)),*

FROM tblDoc2

WHERE LeftBracket < RightBracket