replace text 长度大于8000

text字段处理的仅有:
  下面的函数和语句可以与   ntext、text   或   image   数据一起使用。
  函数                     语句
  DATALENGTH         READTEXT
  PATINDEX             SET   TEXTSIZE
  SUBSTRING           UPDATETEXT
  TEXTPTR               WRITETEXT
  TEXTVALID

  主题：text字段
  1:替换

  --创建数据测试环境
  create   table   #tb(aa   text)
  insert   into   #tb   select   'abc123abc123,asd'

  --定义替换的字符串
  declare   @s_str   varchar(8000),@d_str   varchar(8000)
  select   @s_str='123'   --要替换的字符串
  ,@d_str='000' --替换成的字符串

  --字符串替换处理
  declare   @p   varbinary(16),@postion   int,@rplen   int
  select   @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1   from   #tb
  while   @postion>0
  begin
  updatetext   #tb.aa   @p   @postion   @rplen   @d_str
  select   @postion=charindex(@s_str,aa)-1   from   #tb
  end

  --显示结果
  select   *   from   #tb

  --删除数据测试环境
  drop   table   #tb

  /****************全部替换************************/
  DECLARE   @ptrval   binary(16)
  SELECT   @ptrval   =   TEXTPTR(aa)     FROM     #tb     WHERE   aa   like   '%数据2%'
  if   @ptrval   is   not   null                 --   一定要加上此句，否则若找不到数据下一句就会报错
  UPDATETEXT   #tb.aa   @ptrval   0   null   '数据3'

  /****************在字段尾添加**********************************/
  --定义添加的的字符串
  declare   @s_str   varchar(8000)
  select   @s_str='*C'   --要添加的字符串
  --字符串添加处理
  declare   @p   varbinary(16),@postion   int,@rplen   int
  select   @p=textptr(detail)   from   test   where   id='001'
  updatetext   test.detail   @p   null   null   @s_str

  总结：
  1:Text字段类型不能直接用replace函数来替换，必须用updatetext
  2:字段比较不能用　where   字段   =   ‘某数据’,可以用like来代替
  3:updatetext时，若@ptrval值为空会出错，需注意。

参考下面的有关TEXT的操作.--更新text字段的值
create table PE_Soft(SoftIntro text,ChannelID integer)
insert into PE_Soft
select  'aaa ',1004
union all
select  'bbb ',1003
union all
select  'ccc ',1002 declare @ptr binary(16)
select @ptr=textptr(SoftIntro) from PE_Soft where ChannelID=1002
updatetext PE_Soft.SoftIntro @ptr null 0  'testing '
select * from PE_Soft drop table PE_Soft
--text字段增加处理--创建测试表
create table test(id varchar(3),detail text)
insert into test
select '001','A*B'--定义添加的的字符串
declare @s_str varchar(8000),@postion int
select @s_str='*C'                 --要添加的字符串
        ,@postion=null                --追加的位置,null 加在尾部,0 加在首部,其他值则加在指定位置--字符串添加处理
declare @p varbinary(16)
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p @postion 0 @s_str--显示处理结果
select * from test
go--删除测试表
drop table test
--text字段的替换处理--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*'         --要替换的字符串
        ,@d_str='+'                --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(txt)
        ,@rplen=len(@s_str)
        ,@postion=charindex(@s_str,txt)-1
from test where id='001'while @postion>0
begin
        updatetext test.txt @p @postion @rplen @d_str
        select @postion=charindex(@s_str,txt)-1 from test
end--显示结果
select * from testgo
--删除数据测试环境
drop table test--text字段的添加处理存储过程--全表--创建测试表
create table [user](uid int,UserLog text)
create table [order](uid int,state bit)insert into [user]
select 1,'a'
union all select 2,'b'
union all select 3,'c'insert into [order]
select 1,1
union all select 2,0
union all select 3,1
go--处理的存储过程
CREATE PROCEDURE spUpdateUserLog
@StrLog text,
@State int
AS
--定义游标,循环处理数据
declare @uid int
declare #tb cursor for select a.uid from [user] a join [order] b on a.uid=b.uid
where state=@stateopen #tb
fetch next from #tb into @uid
while @@fetch_status=0
begin
        --字符串添加处理
        declare @p varbinary(16)
        select @p=textptr(UserLog) from [user] where uid=@uid
        updatetext [user].UserLog @p null 0 @StrLog
        fetch next from #tb into @uid
end
close #tb
deallocate #tb
go--调用示例:
exec spUpdateUserLog '123',1--显示处理结果
select * from [user]go--删除测试环境
drop table [user],[order]
drop proc spUpdateUserLog/*--测试结果uid         UserLog
----------- ----------
1           a123
2           b
3           c123（所影响的行数为 3 行）
--*/
--text字段的替换处理--全表替换--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
union all select '002','A*B-AA*BB'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*'         --要替换的字符串
        ,@d_str='+'                --替换成的字符串
--定义游标,循环处理数据
declare @id varchar(3)
declare #tb cursor for select id from test
open #tb
fetch next from #tb into @id
while @@fetch_status=0
begin
        --字符串替换处理
        declare @p varbinary(16),@postion int,@rplen int
        select @p=textptr(txt)
                ,@rplen=len(@s_str)
                ,@postion=charindex(@s_str,txt)-1
        from test where id=@id

        while @postion>0
        begin
                updatetext test.txt @p @postion @rplen @d_str
                select @postion=charindex(@s_str,txt)-1 from test where id=@id
        end        fetch next from #tb into @id
end
close #tb
deallocate #tb--显示结果
select * from testgo
--删除数据测试环境
drop table test************************
支持text字段处理的仅有:
下面的函数和语句可以与 ntext、text 或 image 数据一起使用。
函数          语句
DATALENGTH    READTEXT
PATINDEX      SET TEXTSIZE
SUBSTRING     UPDATETEXT
TEXTPTR       WRITETEXT
TEXTVALID
1:替换--创建数据测试环境
create table #tb(aa text)
insert into #tb select 'abc123abc123,asd'--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123' --要替换的字符串
,@d_str='000' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
updatetext #tb.aa @p @postion @rplen @d_str
select @postion=charindex(@s_str,aa)-1 from #tb
end--显示结果
select * from #tb--删除数据测试环境
drop table #tb/****************全部替换************************/
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(aa)  FROM  #tb  WHERE aa like '%数据2%'
if @ptrval is not null        -- 一定要加上此句，否则若找不到数据下一句就会报错
UPDATETEXT #tb.aa @ptrval 0 null '数据3'
/****************在字段尾添加**********************************/
--定义添加的的字符串
declare @s_str varchar(8000)
select @s_str='*C'   --要添加的字符串
--字符串添加处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p null null @s_str
总结：
1:Text字段类型不能直接用replace函数来替换，必须用updatetext
2:字段比较不能用　where 字段 = ‘某数据’,可以用like来代替
3:updatetext时，若@ptrval值为空会出错，需注意

数据不多的话，就到处到xls然后替换再导入数据多的话，试试varchar(max)

--两个支持text字段内文字替换的存储过程
--csdn realgz
--(http://topic.csdn.net/u/20080505/20/d2dffbbe-6d6b-41cf-b29a-41149540eafa.html?seed=562172452) 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

调试易

replace text 长度大于8000

解决方案 »