create table #tblDoc
(
	FromSender varchar(150)
)

insert #tblDoc
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 'Some longer name <LongEmailAddressHere@LongDomainName.com>' union all
select 'Short <e@e.e>'


SELECT substring(FromSender, CHARINDEX('<', FromSender) + 1, CHARINDEX('>', FromSender) - CHARINDEX('<', FromSender) - 1)
from #tblDoc

drop table #tblDoc

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 'Lowell Mumble Mumble <Lowell@somedomain.com>'
),
tblDoc2 
As
(
SELECT 
  CHARINDEX('<', [From]) As LeftBracket,
  CHARINDEX('>', [From]) As RightBracket,
[FROM]
FROM tblDoc
)
SELECT SUBSTRING([From],LeftBracket +1,RightBracket - (LeftBracket +1)),*
FROM tblDoc2

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

;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