declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='http://10.10.0.1/' --要替换的字符串
,@d_str='http://10.21.3.1/' --替换成的字符串
declare @id int
declare #rpc cursor for select AnnounceID from dv_bbs1
open #rpc
fetch next from #rpc into @id
while @@fetch_status=0
begin
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(body)
,@rplen=len(@s_str)
,@postion=charindex(@s_str,body)-1
from dv_bbs1 where AnnounceID=@id
while @postion>0
begin
updatetext dv_bbs1.body @p @postion @rplen @d_str
select @postion=charindex(@s_str,body)-1 from dv_bbs1 where AnnounceID=@id end
fetch next from #rpc into @id
end
close #rpc
deallocate #rpc
--創建測試環境
Create Table flag(addre Varchar(1000))
--插入數據
Insert flag Select ''
Union All Select Null
Union All Select 'http://10.10.0.1/***/****.exe'
Union All Select 'http://10.10.0.2/***/****.exe'
GO
--測試
Update flag Set addre = Replace(addre, 'http://10.10.0.1/', 'http://10.21.3.1/')
Select * From flag
GO
--刪除測試環境
Drop Table flag
--結果
/*
addre