CSDN-CSDN社区-MS-SQL Server-非技术版

收藏 好久不写sql 代码了，写了两个支持text字段内文字替换的存储过程，可能对一些人有用，共享[问题点数:0分，结帖人:realgz]

  • realgz
  • (realgz)
  • 等　级：
  • 结帖率：
楼主发表于：2008-05-05 20:24:52
use master
go
use master
go

if object_id('sp_ReplaceTextCol') is not null
drop  procedure sp_ReplaceTextCol
go

alter procedure sp_ReplaceTextCol
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*
为一个表内的text做统一替换的存储过程
缺点：支持且仅支持主键为一个列的表
可见改进：可以写个只有一列的临时表 来支持多主键,更新的时候按照这个临时表来做
realgz 2008-05-05

*/
as
begin

declare @exec nvarchar(4000),@keyName sysname,@keyValue sql_variant
declare @rpPtr varbinary(16),@rpPostion int,@rpLen int

select @keyName = sc.name  from
sysobjects so
join  sysindexes idx on so.parent_obj=idx.id
join sysindexkeys idk on so.parent_obj=idk.id and idx.indid = idk.indid
join syscolumns sc on so.parent_obj=sc.id and idk.colid=sc.colid
where so.xtype='PK' and so.parent_obj =object_id(@tableName)
if @@rowcount <> 1
begin
raiserror ('表不符合要求或者没有这个表',12,1)
return
end


select @rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%'
select @exec ='declare #c cursor static  for  select ['+@keyName+'],textptr('+@colname+'),PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1 from ' +@tablename+' '+@whereStr
--print @exec
exec sp_executesql @exec
open #c
fetch next from #c into @keyValue,@rpPtr,@rpPostion
while @@fetch_status=0
begin
--select @keyValue,@rpPostion
  while @rpPostion>0
  begin
  select @exec= 'updatetext '+@tablename+'.'+@colname+' @rpPtr @rpPostion  @rpLen @NewStr'
exec sp_executesql @exec,N' @rpPtr varbinary(16),@rpPostion int,@rpLen int,@newStr nvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr
select @exec=' select @rpPostion = PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1 from ' +@tablename+' where ['+@keyName+'] = @keyValue'
exec sp_executesql @exec,N'@keyValue sql_variant ,@rpPostion int output',@keyValue,@rpPostion output
  end
fetch next from #c into @keyValue,@rpPtr,@rpPostion
end
close #c
deallocate #c

end

go

if object_id('sp_ReplaceTextWithMultiColPk') is not null
drop  proc sp_ReplaceTextWithMultiColPk
go

create procedure sp_ReplaceTextWithMultiColPk
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*
为一个表内的text做统一替换的存储过程，sp_ReplaceTextCol升级版本
缺点：慢，而且事实上还没办法支持任意表，而且代码看起来难受
因为 varchar的长度有限，当然还可以扩展，不过在sql 2005 里已经没这个问题了，懒得再写。
realgz 2008-05-05

*/
as
begin
set nocount on
declare @exec nvarchar(4000),@fetch nvarchar(4000),@insert nvarchar(4000),@where nvarchar(4000)
declare @rpPtr varbinary(16),@rpPostion int,@rpLen int
if object_id('tempdb..#key') is not null
drop table #key

--获得主键列表
select  sc.name keyName  into #key  from
sysobjects so
join  sysindexes idx on so.parent_obj=idx.id
join sysindexkeys idk on so.parent_obj=idk.id and idx.indid = idk.indid
join syscolumns sc on so.parent_obj=sc.id and idk.colid=sc.colid
where so.xtype='PK' and so.parent_obj =object_id(@tableName) and idx.status & 0x800>0

if @@rowcount < 1
begin
raiserror ('表不符合要求或者没有这个表',12,1)
return
end


--增加键值列
alter table #key add keyValue sql_variant
alter table #key add primary key(keyName)

--为游标的提取生成语句
select @fetch='',@insert='',@where=''
select @fetch = @fetch +'declare @'+keyName +' sql_variant '+char(13) from #key
select @fetch = @fetch +'fetch next  from #c into @rpPtr,@rpPostion'
select @fetch = @fetch +',@'+keyName +char(13)  from #key

--把游标提取出来的值放到临时表的语句
select @insert = @insert +' insert into #key(keyName,keyValue ) '
select @insert = @insert + ' select '''+replace(keyName,'''','''''')+''',@'+keyName+' union all'  +char(13)  from #key
select @insert = left(@insert,len(@insert)-10)


select @fetch=@fetch +@insert

--替换的长度
select @rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%'


--游标声明语句
select @exec ='declare #c cursor static  for  select textptr('+@colname+'),PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1'
select @exec = @exec +',['+keyName+']'  from #key 

select @exec = @exec +' from ' +@tablename+' '+@whereStr


--每次获得游标行对应text指针的语句
select @where =' where 1=1'
select @where =@where +' and ['+keyName+']= (select keyValue from #key where keyName = '''+replace(keyName,'''','''''')+''')' from #key

--声明游标
exec sp_executesql @exec
truncate table #key
open #c

--提取第一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int  output',@rpPtr output,@rpPostion  output
--print @fetch

while @@fetch_status=0
begin

--只要@rpPostion>0 证明还需要替换
  while @rpPostion>0
  begin
--替换
  select @exec= 'updatetext '+@tablename+'.'+@colname+' @rpPtr @rpPostion  @rpLen @NewStr'
exec sp_executesql @exec,N' @rpPtr varbinary(16),@rpPostion int,@rpLen int,@newStr nvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr

--重新判断是否需要替换
select @exec=' select @rpPostion = PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1 from ' +@tablename+@where
exec sp_executesql @exec,N'@rpPostion int output',@rpPostion output
  end
truncate table #key

--提取下一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int output',@rpPtr output,@rpPostion  output
end
close #c
deallocate #c
if object_id('tempdb..#key') is not null
drop table #key
set nocount off
end

go
回复次数：11
#1楼 得分：0回复于：2008-05-05 21:48:27
up
  • yiyi_wx用户头像
  • yiyi_wx
  • (依依(塞翁失马焉知非福))
  • 等　级：
#2楼 得分：0回复于：2008-05-05 21:54:47
mark~
lz真好~
  • realgz用户头像
  • realgz
  • (realgz)
  • 等　级：
#3楼 得分：0回复于：2008-05-05 23:07:48
--这样代码好看点，无他
if object_id('sp_replaceTextWithMultiColPk') is not null
drop  proc sp_replaceTextWithMultiColPk
go

create procedure sp_replaceTextWithMultiColPk
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*
为一个表内的text做统一替换的存储过程
缺点：慢，而且事实上还没办法支持任意表，而且代码看起来难受
因为 varchar的长度有限，当然还可以扩展，不过在sql 2005 里已经没这个问题了，懒得再写。
realgz 2008-05-05

*/
as
begin
set nocount on
declare @cursor nvarchar(4000),@fetch nvarchar(4000),@insert nvarchar(4000),@where nvarchar(4000)
declare @replaceExec nvarchar(4000),@checkExec nvarchar(4000)
declare @rpPtr varbinary(16),@rpPostion int,@rpLen int
if object_id('tempdb..#key') is not null
drop table #key

--获得主键列表
select  sc.name keyName  into #key  from
sysobjects so
join  sysindexes idx on so.parent_obj=idx.id
join sysindexkeys idk on so.parent_obj=idk.id and idx.indid = idk.indid
join syscolumns sc on so.parent_obj=sc.id and idk.colid=sc.colid
where so.xtype='PK' and so.parent_obj =object_id(@tableName) and idx.status & 0x800>0

if @@rowcount < 1
begin
raiserror ('表不符合要求或者没有这个表',12,1)
return
end


--增加键值列
alter table #key add keyValue sql_variant
alter table #key add primary key(keyName)

--替换的长度
select @rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%'

--游标声明语句
select @cursor ='declare #c cursor static  for  select textptr('+@colname+'),PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1'
select @cursor = @cursor +',['+keyName+']'  from #key 

select @cursor = @cursor +' from ' +@tablename+' '+@whereStr



--为游标的提取生成语句
select @fetch='',@insert='',@where=''
select @fetch = @fetch +'declare @'+keyName +' sql_variant '+char(13) from #key
select @fetch = @fetch +'fetch next  from #c into @rpPtr,@rpPostion'
select @fetch = @fetch +',@'+keyName +char(13)  from #key

--把游标提取出来的值放到临时表的语句
select @insert = @insert +' insert into #key(keyName,keyValue ) '
select @insert = @insert + ' select '''+replace(keyName,'''','''''')+''',@'+keyName+' union all'  +char(13)  from #key
select @insert = left(@insert,len(@insert)-10)


select @fetch=@fetch +@insert


--每次获得游标行对应text指针的语句
select @where =' where 1=1'
select @where =@where +' and ['+keyName+']= (select keyValue from #key where keyName = '''+replace(keyName,'''','''''')+''')' from #key

--改写的sql
select @replaceExec =  'updatetext '+@tablename+'.'+@colname+' @rpPtr @rpPostion  @rpLen @NewStr'

--检查是否存在目标的sql
select @checkExec =' select @rpPostion = PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1 from ' +@tablename+@where



--声明游标
exec sp_executesql @cursor
truncate table #key
open #c

--提取第一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int  output',@rpPtr output,@rpPostion  output
--print @fetch

while @@fetch_status=0
begin

--只要@rpPostion>0 证明还需要替换
  while @rpPostion>0
  begin
--替换

exec sp_executesql @replaceExec,N' @rpPtr varbinary(16),@rpPostion int,@rpLen int,@newStr nvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr

--重新判断是否需要替换
exec sp_executesql @checkExec,N'@rpPostion int output',@rpPostion output
  end
truncate table #key

--提取下一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int output',@rpPtr output,@rpPostion  output
end
close #c
deallocate #c
if object_id('tempdb..#key') is not null
drop table #key
set nocount off
end

go
#4楼 得分：0回复于：2008-05-05 23:15:12
good
#5楼 得分：0回复于：2008-05-05 23:25:44
mark
#6楼 得分：0回复于：2008-05-05 23:28:53
sign
  • roy_88用户头像
  • roy_88
  • (中国风)
  • 等　级：

  • 2

    4
#7楼 得分：0回复于：2008-05-05 23:55:11
看看
#8楼 得分：0回复于：2008-05-05 23:55:25
干吗使的？长长的字母看着腰疼
  • realgz用户头像
  • realgz
  • (realgz)
  • 等　级：
#9楼 得分：0回复于：2008-05-06 08:16:00
use master
go
/*

万一昨天有收集我这个过程的同志注意了：存在严重bug，就是第一个字母不能是查找内容
原来的替换语句是抄的（本来就是因为网上搜来的代码无法工作我才自己写，能少写几个单词我就必定抄），因为可以用所以就将就用了，
结果早上睡醒忽然想到一个严重bug，于是起来改掉了上面说的内容
谴责下自己抄别人代码的时候不注意阅读，也谴责下到处乱贴代码不做检查的网友

*/

if object_id('sp_replaceTextCol') is not null
drop  proc sp_replaceTextWithMultiColPk
go

create procedure sp_replaceTextWithMultiColPk
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*
作用：为一个表内的text做统一替换的存储过程
版本:1.0
缺点：慢，事实上还没办法支持任意表
因为 varchar的长度有限，当然还可以扩展，不过在sql 2005 里已经没这个问题了，懒得再写。
realgz 2008-05-06

*/
as
begin
set nocount on
declare @cursor nvarchar(4000),@fetch nvarchar(4000),@insert nvarchar(4000),@where nvarchar(4000)
declare @replaceExec nvarchar(4000),@checkExec nvarchar(4000)
declare @rpPtr varbinary(16),@rpPostion int,@rpLen int
if object_id('tempdb..#key') is not null
drop table #key

--获得主键列表
select  sc.name keyName  into #key  from
sysobjects so
join  sysindexes idx on so.parent_obj=idx.id
join sysindexkeys idk on so.parent_obj=idk.id and idx.indid = idk.indid
join syscolumns sc on so.parent_obj=sc.id and idk.colid=sc.colid
where so.xtype='PK' and so.parent_obj =object_id(@tableName) and idx.status & 0x800>0

if @@rowcount  < 1
begin
raiserror ('表不符合要求或者没有这个表',12,1)
return
end


--增加键值列
alter table #key add keyValue sql_variant
alter table #key add primary key(keyName)

--替换的长度
select @rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%'

--游标声明语句
select @cursor ='declare #c cursor static  for  select textptr('+@colname+'),PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')'
select @cursor = @cursor +',['+keyName+']'  from #key

select @cursor = @cursor +' from ' +@tablename+' '+@whereStr


--为游标的提取生成语句
select @fetch='',@insert='',@where=''
select @fetch = @fetch +'declare @'+keyName +' sql_variant '+char(13) from #key
select @fetch = @fetch +'fetch next  from #c into @rpPtr,@rpPostion'
select @fetch = @fetch +',@'+keyName +char(13)  from #key

--把游标提取出来的值放到临时表的语句
select @insert = @insert +' insert into #key(keyName,keyValue ) '
select @insert = @insert + ' select '''+replace(keyName,'''','''''')+''',@'+keyName+' union all'  +char(13)  from #key
select @insert = left(@insert,len(@insert)-10)


select @fetch=@fetch +@insert


--每次获得游标行对应text指针的语句
select @where =' where 1=1'
select @where =@where +' and ['+keyName+']= (select keyValue from #key where keyName = '''+replace(keyName,'''','''''')+''')' from #key

--改写的sql
select @replaceExec =  'updatetext '+@tablename+'.'+@colname+' @rpPtr @rpPostion  @rpLen @NewStr'

--检查是否存在目标的sql
select @checkExec =' select @rpPostion = PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+') from ' +@tablename+@where


--声明游标
exec sp_executesql @cursor
truncate table #key
open #c

--提取第一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int  output',@rpPtr output,@rpPostion  output
--print @fetch

while @@fetch_status=0
begin

--只要@rpPostion>0 证明还需要替换
while @rpPostion>0
begin
--替换
select @rpPostion=@rpPostion-1
exec sp_executesql @replaceExec,N' @rpPtr varbinary(16),@rpPostion int,@rpLen int,@newStr nvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr

--重新判断是否需要替换
exec sp_executesql @checkExec,N'@rpPostion int output',@rpPostion output
end
truncate table #key

--提取下一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int output',@rpPtr output,@rpPostion  output
end
close #c
deallocate #c
if object_id('tempdb..#key') is not null
drop table #key
set nocount off
end


go

use tempdb
go
create table test
(id int identity , b ntext
primary key(id)
)
go
declare @x int
select @x=1
while @x <5
begin
insert into test(b) select replicate(newid(),100)
select @x=@x+1
end
go
select *  from test
go
exec sp_replaceTextCol 'test','b','d','$dd$'
go
select * from test
go
drop table test
go
  • realgz用户头像
  • realgz
  • (realgz)
  • 等　级：
#10楼 得分：0回复于：2008-05-06 08:18:48
代码贴错了
  • realgz用户头像
  • realgz
  • (realgz)
  • 等　级：
#11楼 得分：0回复于：2008-05-06 08:34:45
请看另一贴，也在非技术版

http://topic.csdn.net/u/20080506/08/b965f771-0712-4711-af6e-995539692e36.html
公司简介| 广告服务| 银行汇款帐号| 联系方式| 版权声明| 问题报告
北京创新乐知广告有限公司 版权所有, 京 ICP 证 070598 号
世纪乐知(北京)网络技术有限公司 提供技术支持
文明办网文明上网举报电话：13552009689 
Copyright © 1999-2009, CSDN.NET, All Rights Reserved
GongshangLogo