SQL Server中的汇总替换? [英] Aggregate replace in SQL Server?
问题描述
我想要实现的是使动态的一系列替换必须在某一领域执行。 (为了使事情变得更加容易,我想实际上要删除数据,所以我将永远与
$ b
相比,有时候我只需要做一个替换:
... REPLACE(myField,stringToRemove,'')
有时,我需要两个替换:
... REPLACE(REPLACE(myField,stringToRemove,''),anotherStringToRemove,'')
然而,我需要为了使这个动态,我不提前知道我将有多少值,所以我需要做多少替换(清除)。
我尝试搜索聚合的字符串操作函数,当然没有,我也知道这可以通过CLR聚合函数来实现,但是我没有可能使用它。
任何想法?
您可以使用FromValue和ToValue设置表变量并使用while循环来进行替换。
- 要在
中替换的表声明@T表
(
)值varchar(50)
)
插入到@T值
('first second third'),
('first second第三个')
- 要替换的字符串的表
declare @Rep表
(
ID int identity主键,
FromValue varchar(50) ,
ToValue varchar(50)
)
插入到@Rep值
('second','fourth'),
('third' ,'第五')
声明@ID int
select @ID = max(ID)
from @Rep
while @ID> 0
begin
update @T
set Value = replace(Value,FromValue,ToValue)
from @Rep
其中ID = @ID
set @ID - = 1
end
从@T
中选择*
结果:
价值
---------- ---------
第一第四第五
第一第四第五
如果你只想查询这些值,你可以这样做。
; C为
pre>
选择0作为ID,
值,
0作为Lvl
从@T
union all
选择R.ID,
cast(将(C.Value,R.FromValue,R.ToValue)替换为varchar(50)),
Lvl + 1
from @Rep as R
inner join C
on C .ID + 1 = R.ID
)
选择顶部1与关系值
从C
订单由Lvl desc
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?
解决方案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
这篇关于SQL Server中的汇总替换?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!