CODEWITHSUNDEEP
sqlsql-server
Ayesha
Ayesha

Reputation: 3

Retrieve email ids from multiple full Email Address using SQL query

Using SQL-server Am trying to retrieve email id from list of full email addresses

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

Upvotes: 0

Views: 980

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

In SQL Server, you can use string_split():

select replace(stuff(s.value, 1, charindex('<', s.value), ''), '>', '')
from t cross apply
     string_split(t.list, ';') s

If you want to re-concatenate them:

select s.emails
from t cross apply
     (select string_agg(replace(stuff(s.value, 1, charindex('<', s.value), ''), '>', ''), ';') as email
      from string_split(t.list, ';') s
     ) s;

EDIT:

Another approach which works with earlier versions of SQL Server is a recursive CTE:

with cte as (
      select convert(varchar(max), null) as email,
             convert(varchar(max), emails) as rest,
             convert(varchar(max), '') as output_emails, 0 as lev
      from (values ('"ABCD"<[email protected]>;"TEST" <[email protected]>; ')
           ) v(emails)
      union all
      select replace(stuff(v.el, 1, charindex('<', v.el), ''), '>;', ''),
             stuff(rest, 1, charindex(';', rest), ''),
             concat(output_emails,
                    replace(stuff(v.el, 1, charindex('<', v.el), ''), '>;', ''),
                    ';'),
             lev + 1
      from cte cross apply
           (values (left(rest, charindex(';', rest)))) v(el) 
      where rest <> '' and lev < 5
     )
select output_emails
from (select cte.*, row_number() over (order by lev desc) as seqnum
      from cte
     ) cte
where seqnum = 1;

Here is a db<>fiddle.

Upvotes: 2

Related Questions