Cleaning up cut and pasted outlook email addresses I often have to create or update e-mail distribution lists in SSMS, where the requestor will ask "please add Anne Hathaway, Albert Einstein, and Ash Ketchum to the DL.  Get their email from Outlook."

Once I put their names in outlook and push the "Check Names" function, it validates they're still in the system.  Copying this list then produces the following output. 

Anne Hathaway &lt; Anne.Hathaway@Email.com &gt;; Albert Einstein &lt; Albert.Einstein@email.com &gt;; Ash Ketchum &lt; Ash.Ketchum@mail.com &gt;


It's a manual process to remove the original name and &lt; > signs, which of course could open itself to error. 

How can I set a variable that, when I paste in the raw data straight from outlook ,it cleans it up so that it gets rid of everything left of the &lt; up to but not including the next encountered ; ?

I'd like the variable to print like so. 
'Anne.Hathaway@email.com; Albert.Einstein@email.com; Ash.Ketchum@mail.com'
You can use a recursive common table expression to iterate through the string variable:

DECLARE @outlook nvarchar(max)='Something.something@email.com;Anne Hathaway &lt; Anne.Hathaway@Email.com &gt;; Albert Einstein &lt; Albert.Einstein@email.com &gt;; Ash Ketchum &lt; Ash.Ketchum@mail.com &gt;';

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT +LEFT(addr, CHARINDEX('&gt;', addr+'&gt;')-1)
FROM (
    SELECT LTRIM(SUBSTRING(part, CHARINDEX('&lt;', part)+1, LEN(part))) AS addr
    FROM semi
    WHERE part IS NOT NULL
    ) AS sub;


Ok, so what's going on here is that the "semi" common table expression splits the string using the semicolon character (and if one of your names has a semicolon in it, you're out of luck).

Once we've split every name/address into its own row, the bottom SELECT gets only the string between the &lt; and &gt; characters.

If you want to go a little crazy with it, you could use XML parsing to concatenate all those lines back on a single row:

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT SUBSTRING(CAST((
    SELECT '; '+LEFT(addr, CHARINDEX('&gt;', addr+'&gt;')-1)
    FROM (
        SELECT LTRIM(SUBSTRING(part, CHARINDEX('&lt;', part)+1, LEN(part))) AS addr
        FROM semi
        WHERE part IS NOT NULL
        ) AS sub
    FOR XML PATH(''), TYPE) AS nvarchar(max)), 3, 10000);


To round off the answer, if you have access to a SQL Server 2017 instance, this is just a few lines of code:

SELECT STRING_AGG(RTRIM(LTRIM(REPLACE(REPLACE(b.[value], '&lt;', ''), '&gt;', ''))), '; ')
FROM STRING_SPLIT(@outlook, ';') AS a
CROSS APPLY STRING_SPLIT(a.[value], ' ') AS b
WHERE b.[value] LIKE '%@%';