Aggregate replace in SQL Server?

问题:

What I'm trying to achieve is to make dynamic a series of replacements that have to be performed on a certain field. (To make things even easier, I want in fact to remove data, so I'll be always comparing with

Say that sometimes I will have to do just one replacement:

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

Sometimes, I will need two replacements:

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

However, I need to make this dynamic and I do not know in advance how many of those values I'll have, and so, how many replacements (removals) I'll have to do.

I tried searching for aggregate string manipulation functions and, of course, there's none. I also know that this can be achieved through a CLR aggregate function but I don't have the possibility of using it.

Any ideas?

回答1:

You can setup a table variable with FromValue and ToValue and use a while loop to do the replacements.

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

Result:

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

If you only want to query the values you can do something like this.

;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

回答2:

You might have to write a scalar function to which you pass the original string, and enough information for it to know which strings to remove, and have it loop through them and return the result of the set of replacements.