SQL Server 中的聚合替换？

Question

我想要实现的目标是使必须在某个字段上执行的一系列替换动态化。 （为了让事情变得更容易，我实际上想要删除数据，所以我会一直与

说有时我只需要进行一次替换：

... REPLACE(myField, stringToRemove, '')

有时，我需要两次替换：

... REPLACE(REPLACE(myField, stringToRemove, ''), anotherStringToRemove, '')

但是，我需要这样做动态的，我事先不知道我将拥有多少个值，因此，我需要进行多少次替换（删除），

我尝试搜索聚合字符串操作函数，当然，没有。我也知道这可以通过以下方式实现 但我无法使用它。

CLR 聚合函数，

Answer 1

您可以使用 FromValue 和 ToValue 设置表变量，并使用 while 循环进行替换。

-- Table to replace in
declare @T table
(
  Value varchar(50)
)

insert into @T values
('first second third'),
('first second third')

-- Table with strings to replace
declare @Rep table
(
  ID int identity primary key,
  FromValue varchar(50),
  ToValue varchar(50)
)

insert into @Rep values
('second', 'fourth'),
('third', 'fifth')

declare @ID int
select @ID = max(ID)
from @Rep

while @ID > 0
begin
  update @T
  set Value = replace(Value, FromValue, ToValue)
  from @Rep
  where ID = @ID

  set @ID -= 1
end

select *
from @T

结果：

Value 
-------------------
first fourth fifth
first fourth fifth

如果您只想查询值，您可以执行以下操作。

;with C as
(
  select 0 as ID, 
         Value,
         0 as Lvl
  from @T
  union all
  select R.ID,
         cast(replace(C.Value, R.FromValue, R.ToValue) as varchar(50)),
         Lvl + 1
  from @Rep as R
    inner join C
      on C.ID + 1 = R.ID
)
select top 1 with ties Value
from C
order by Lvl desc

Answer 2

一旦实现了下面的 CLR 聚合函数，您就可以执行以下操作：

SELECT dbo.ReplaceAgg(t.[text], w.badword, w.goodword) // call CLR aggregate function
FROM [Texts] t CROSS JOIN BadWords w
GROUP BY t.[text]

C# 中的 CLR 聚合函数

/// <summary>
/// Allows to apply regex-replace operations to the same string.
/// For example:
/// SELECT dbo.ReplaceAgg(t.[text], w.badpattern, "...") 
/// FROM [Texts] t CROSS JOIN BadPatterns w
/// GROUP BY t.[text]
/// </summary>
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, 
    IsInvariantToDuplicates = true, IsInvariantToOrder = false, 
    IsInvariantToNulls = true, MaxByteSize = -1)]
public class RegexReplaceAgg : IBinarySerialize
{
    private string str;
    private string needle;
    private string replacement;
    public void Init()
    {
        str = null;
        needle = null;
        replacement = null;
    }
    public void Accumulate(SqlString haystack, SqlString needle, SqlString replacement)
    {
        // Null values are excluded from aggregate.
        if (needle.IsNull) return;
        if (replacement.IsNull) return;
        if (haystack.IsNull) return;
        str = str ?? haystack.Value;
        this.needle = needle.Value;
        this.replacement = replacement.Value;
        str = Regex.Replace(str, this.needle, this.replacement, RegexOptions.Compiled | RegexOptions.CultureInvariant);
    }

    public void Merge(RegexReplaceAgg group)
    {
        Accumulate(group.Terminate(), new SqlString(needle), new SqlString(replacement));
    }

    public SqlString Terminate() => new SqlString(str);

    public void Read(BinaryReader r)
    {
        str = r.ReadString();
        needle = r.ReadString();
        replacement = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        w.Write(str);
        w.Write(needle);
        w.Write(replacement);
    }
}

Answer 3

您可能必须编写一个标量函数，向其中传递原始字符串和足够的信息以使其知道要删除哪些字符串，并让它循环遍历它们并返回替换集的结果。