转：sql里text字段文本替换 最终版本 不做修正了

use master
go


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

create procedure sp_replaceTextCol
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*

万一昨天有收集我这个过程的同志注意了：存在严重bug，就是第一个字母不能是查找内容
原来的替换语句是直接抄的（本来就是因为网上搜来的代码无法工作我才自己写，能少写几个单词我就必定抄），因为可以用所以就将就用了，
结果早上睡醒忽然想到一个严重bug，于是起来改掉了上面说的内容
谴责下自己抄别人代码的时候不注意阅读，也谴责下到处乱贴代码不做检查的网友 
然后这个修改还是有严重bug：查找内容不能是替换内容的一部分……
使用charindex代替patindex可以修正此bug，不过会导致另一个bug：第4000个以后的文本不被处理
使用substring可以完美解决此问题，不过需要注意substring不要把搜索字符串截断掉
随便找种语言都基本上是不超过10行代码搞定的事情，在t-sql里却要多写那么多代码，于是懒得再写了。

*/
/*
作用：为一个表内的text做统一替换的存储过程
版本:1.0
缺点：慢，事实上还没办法支持任意表
bug：查找内容不能是替换内容的一部分
因为 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<16
begin
insert into test(b) select replicate(newid(),100)
select @x=@x+1
end
go
select * from test
go
exec sp_replaceTextCol 'test','b','1','$$'
go
select * from test
go
drop table test
go

----------------------------------------------

刚刚发现有这功能，好用。
可惜我不怎么热衷发贴了。阿弥陀佛。

SQL code

use master go if object_id('sp_replaceTextCol') is not nulldrop proc sp_replaceTextCol go create procedure sp_replaceTextCol @tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''/* 万一昨天有收集我这个过程的同志注意了：存在严重bug，就是第一个字母不能是查找内容 原来的替换语句是直接抄的（本来就是因为网上搜来的代码无法工作我才自己写，能少写几个单词我就必定抄），因为可以用所以就将就用了， 结果早上睡醒忽然想到一个严重bug，于是起来改掉了上面说的内容 谴责下自己抄别人代码的时候不注意阅读，也谴责下到处乱贴代码不做检查的网友 然后这个修改还是有严重bug：查找内容不能是替换内容的一部分…… 使用charindex代替patindex可以修正此bug，不过会导致另一个bug：第4000个以后的文本不被处理 使用substring可以完美解决此问题，不过需要注意substring不要把搜索字符串截断掉 随便找种语言都基本上是不超过10行代码搞定的事情，在t-sql里却要多写那么多代码，于是懒得再写了。 *//*作用：为一个表内的text做统一替换的存储过程 版本:1.0 缺点：慢，事实上还没办法支持任意表 bug：查找内容不能是替换内容的一部分 因为 varchar的长度有限，当然还可以扩展，不过在sql 2005 里已经没这个问题了，懒得再写。 realgz 2008-05-06 */asbegin 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 offend go use tempdb gocreate table test (id int identity , b ntextprimary key(id) ) godeclare @x intselect @x=1while @x<16begin insert into test(b) select replicate(newid(),100) select @x=@x+1endgoselect * from test goexec sp_replaceTextCol 'test','b','1','$$'goselect * from test godrop table test go