Sql-Server-2008-R2

清理剪切和粘貼的 Outlook 電子郵件地址

  • June 23, 2018

我經常需要在 SSMS 中創建或更新電子郵件分發列表,請求者會在其中詢問“請將 Anne Hathaway、Albert Einstein 和 Ash Ketchum 添加到 DL。從 Outlook 獲取他們的電子郵件。”

一旦我將他們的名字放在 Outlook 中並按下“檢查名字”功能,它就會驗證他們仍在系統中。然後複製此列表會產生以下輸出。

Anne Hathaway < Anne.Hathaway@Email.com >; Albert Einstein < Albert.Einstein@email.com >; Ash Ketchum < Ash.Ketchum@mail.com >

這是刪除原始名稱和 < > 符號的手動過程,這當然可能會導致錯誤。

我如何設置一個變數,當我直接從 Outlook 粘貼原始數據時,它會清理它,以便它擺脫 < 直到但不包括下一個遇到的所有內容;?

我希望變數像這樣列印。‘Anne.Hathaway@email.com; Albert.Einstein@email.com;Ash.Ketchum@mail.com’

您可以使用遞歸公用表表達式來遍歷字元串變數:

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;

好的,所以這裡發生的是“半”公用表表達式使用分號字元拆分字元串(如果您的名字中有一個分號,那麼您就不走運了)。

一旦我們將每個名稱/地址拆分到自己的行中,底部將僅獲取和字元SELECT之間的字元串。&lt;``&gt;

如果你想對它有點瘋狂,你可以使用 XML 解析將所有這些行連接回一行:

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);

為了完善答案,如果您有權訪問 SQL Server 2017 實例,這只是幾行程式碼:

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 '%@%';

引用自:https://dba.stackexchange.com/questions/210393