批量替换SQL SERVER库中ntext字段的存储过程

最新推荐文章于 2019-06-13 06:38:47 发布

原创最新推荐文章于 2019-06-13 06:38:47 发布 · 1k 阅读

CC 4.0 BY-SA版权

本文介绍了一个SQL Server存储过程的实现，该过程用于遍历数据库表中的所有记录，并批量替换指定字段内的字符串。通过定义两个不同的游标，分别完成两种不同的字符串替换任务。

USE [NewsPaper]
GO

/****** Object: StoredProcedure [dbo].[ReplaceMyStr] Script Date: 11/07/2013 02:03:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReplaceMyStr]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReplaceMyStr]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[ReplaceMyStr]
as

declare @ID int
declare @strtmp varchar(max)
declare @strsrc char(25),@strdsc char(10)
declare @strsrc2 char(12),@strdsc2 char(15)
set @strsrc = '<img src= ../..'
set @strdsc = '<img src="'
set @strsrc2 = '.jpg></P><P>'
set @strdsc2 = '.jpg" /><P></P>'

declare replace_Cursor scroll Cursor
for
select id from yao_article
--for read only
open replace_Cursor
fetch next from replace_Cursor into @ID
while @@fetch_status=0
begin
select @strtmp = [Content] from yao_article where id=@ID
select @strtmp = Replace(@strtmp,@strsrc,@strdsc)
update yao_article set [Content] = @strtmp where id=@ID

fetch next from replace_Cursor into @ID
end
close replace_Cursor
deallocate replace_Cursor

declare replace_Cursor2 scroll Cursor
for
select id from yao_article
--for read only
open replace_Cursor2
fetch next from replace_Cursor2 into @ID
while @@fetch_status=0
begin

select @strtmp = [Content] from yao_article where id=@ID
select @strtmp = Replace(@strtmp,@strsrc2,@strdsc2)
update yao_article set [Content] = @strtmp where id=@ID

fetch next from replace_Cursor2 into @ID
end
close replace_Cursor2
deallocate replace_Cursor2