T-SQL Search and replace pattern

Question

My brain just isn't working today. I have a string that has a comma separated list of email addresses similar to below

removethis<[email protected]>; andthis<[email protected]>; [email protected]; butthis<[email protected]>

I would like to just return a semicolon separated list of email address so the output I am looking for would be something like this

[email protected];[email protected];[email protected];[email protected]

I would like to achieve this in a T-SQL function so I can reuse this if possible.

The list that gets passed as seen in example above can either be just an email address or a name followed by email between. The logic needs to check if it's just an email then return it, if it's a name and an email (indicated by pattern name < email > then just return what's between the <>

for info: SQL version: Azure SQL Database

Example scenario:

DECLARE @X varchar(max) = '
removethis<[email protected]>;andthis<[email protected]>;[email protected];butthis<[email protected]>'
Select @udfFormatEmail(@X)

Expected output:

[email protected];[email protected];[email protected];[email protected]

Answer 1

You could do this using a combination of string_split and string_agg and stuff to remove all the characters before each '<'.

declare @X varchar(max) ='removethis<[email protected]>;andthis<[email protected]>;[email protected];butthis<[email protected]>'

select String_Agg(Replace(Stuff(value,1, CharIndex('<',value),''),'>',''),';')
from String_Split(@X,';')