使用 MS SQL 2008，我需要洗掉包含在文本字串中的電子郵件地址。IE：

“這是一個帶有多個電子郵件地址的示例文本行，例如 [email protected]，或者我也可以使用 [email protected] 甚至某人@somewhere.pl 來混淆”。

期望的結果是：

“這是一個包含多個電子郵件地址的示例文本行，或者我也可以擁有甚至混合起來”

甚至：

“這是一個包含多個電子郵件地址的示例文本行，例如 fred@***** 或者我也可以使用 bert@***** 甚至某人@***** 來混淆”。

洗掉某個字符左側或右側的所有內容的例子有很多，但沒有洗掉固定字符左側或右側的文本，直到第一個空格。任何幫助表示贊賞。

uj5u.com熱心網友回復：

假設文本字串在表列中，您可以在 SQL Server 2008 中借助字串吐出函式和通過for xml. 在較新版本的 SQL Server 中（您應該真正嘗試遷移到該版本），使用類似string_agg而不是stuff( ... for xml )組合的功能可以更輕松地實作這一點。

這種實作是貪婪的，因為它將掩蓋兩個空格（或字串的開頭/結尾）之間的任何字符組合，其中至少包含一個@字符。fullsentences-like^this'one%will￡be$included,so\long)as/there>is.a=@*character&somewhere以及僅由一個或多個@字符組成的字串。

我將把如何處理不是電子郵件地址的字串留給你。

詢問

with t as
(
    select *
    from(values('This is a sample line of text with multiple email addresses like [email protected] or I could also have [email protected] or even [email protected] to mix things up')
              ,('This is another sample line of text with multiple email addresses like [email protected] or I could also have [email protected] or even [email protected] to mix things up')
              ,('fullsentences-like^this''one%will￡be$included,so\long)as/there>is.a=@*character&somewhere')
              ,('@')
              ,('a @@@@@ b')
              ,('@ @ @ @ @')
              ,('Let''s meet @ the beach')
        ) as s(s)
)
,s as
(
    select t.s
          ,s.rn
          ,case when charindex('@',s.item) > 0 then '***' else s.item end as item
    from t
        cross apply dbo.fn_StringSplit4k(t.s,' ',null) as s
)
select t.s
      ,stuff((select ' '   s.item
              from s
              where t.s = s.s
              order by s.rn
              for xml path('')
             )
            ,1,1,''
            ) as s
from t;

輸出

字串拆分功能

create function [dbo].[fn_StringSplit4k]
(
     @str nvarchar(4000) = ' '              -- String to split.
    ,@delimiter as nvarchar(1) = ','        -- Delimiting value to split on.
    ,@num as int = null                     -- Which value to return.
)
returns table
as
return
                    -- Start tally table with 10 rows.
    with n(n)   as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)

                    -- Select the same number of rows as characters in @str as incremental row numbers.
                    -- Cross joins increase exponentially to a max possible 10,000 rows to cover largest @str length.
        ,t(t)   as (select top (select len(isnull(@str,'')) a) row_number() over (order by (select null)) from n n1,n n2,n n3,n n4)

                    -- Return the position of every value that follows the specified delimiter.
        ,s(s)   as (select 1 union all select t 1 from t where substring(isnull(@str,''),t,1) = @delimiter)

                    -- Return the start and length of every value, to use in the SUBSTRING function.
                    -- ISNULL/NULLIF combo handles the last value where there is no delimiter at the end of the string.
        ,l(s,l) as (select s,isnull(nullif(charindex(@delimiter,isnull(@str,''),s),0)-s,4000) from s)
    
    select rn
          ,item
    from(select row_number() over(order by s) as rn
               ,substring(@str,s,l) as item
        from l
        ) a
    where rn = @num
       or @num is null;

標籤：

上一篇：在SQLServer2008中將多個SQL行連接成單個列

下一篇：SQL根據from和to日期查詢出生日期和月份