Retrieve email id from full email

Question

Hi there,

 

I need to take email id from full email address.

 

Example 

Input 

 "ABCD"<[email protected]>;"TEST" <[email protected]>; 

 

Needed output

 [email protected],[email protected]

 

i have tried the below query but it is giving only one email id not all.Some help will be greatly appreciated.

SELECT  CASE WHEN to_address LIKE '%<%' THEN SUBSTRING(to_address,CHARINDEX('<',to_address)+1,CHARINDEX('>',to_address)-CHARINDEX('<',to_address)-1) ELSE to_address END AS ToAddress from test