SQL Server中TEXT/NTEXT字段内容替换方法总结(SQL 2005及以上建议使用VARCHAR(MAX)/NVARCHAR(MAX)代替)

1.update ntext:
(1)varchar和nvarchar类型是支持replace，所以如果你的text/ntext不超过8000/4000可以先转换成前面两种类型再使用replace。
    update 表名 
    set text类型字段名=replace(convert(varchar(8000),text类型字段名),'要替换的字符','替换成的值')
    update 表名 
    set ntext类型字段名=replace(convert(nvarchar(4000),ntext类型字段名),'要替换的字符','替换成的值') 
(2)如果text/ntext超过8000/4000,看如下例子
   declare @pos int
   declare @len int
   declare @str nvarchar(4000)
   declare @des nvarchar(4000)
   declare @count int   
   set @des ='<requested_amount+1>'--要替换成的值
   set @len=len(@des)
   set @str= '<requested_amount>'--要替换的字符
   set @count=0--统计次数.
   WHILE 1=1
   BEGIN
       select @pos=patINDEX('%'+@des+'%',propxmldata) - 1
       from 表名
       where 条件 
      IF @pos>=0
      begin
          DECLARE @ptrval binary(16)
          SELECT @ptrval = TEXTPTR(字段名) 
          from 表名
          where 条件
          UPDATETEXT 表名.字段名 @ptrval @pos @len @str
          set @count=@count+1
       end
      ELSE
         break;
   END
   select @count
2.alter column语句有局限性,比如不允许修改text、image、ntext 或 timestamp 列.
以下提供一个修改ntext列的例子:
    Alter Table tbl Add newcol ntext null
    go
    update tbl set newcol=col
    go
    EXEC sp_rename 'tbl.col', 'oldcol', 'COLUMN'
    go
    EXEC sp_rename 'tbl.newcol', 'col', 'COLUMN'
    go
    alter table tbl drop column oldcol
    go
以上通过新增一列替换旧的列方法实现了将一个不允许为空的ntext修改为允许为空的ntext列(注意:以上的go不能缺少).修改表结构之后,由于视图所依赖的基础对象的更改，视图的持久元数据会过期,需要刷新视图,通过sp_refreshview (可以通过sp_depends 找处相关的视图,再通过sp_refreshview逐个刷新).
另外可以也可以通过一下存储过程进行刷新所有视图:
   PRINT 'Refreshing all views'
   DECLARE @vName sysname
   DECLARE refresh_cursor CURSOR FOR 
    SELECT Name from sysobjects WHERE xtype = 'V'
    order by crdate
   FOR READ ONLY  
   OPEN refresh_cursor
   FETCH NEXT FROM refresh_cursor 
   INTO @vName
    WHILE @@FETCH_STATUS <> -1
    BEGIN
        exec sp_refreshview @vName
        PRINT '视图' + @vName + ' refreshed'
       FETCH NEXT FROM refresh_cursor 
       INTO @vName
    END
   CLOSE refresh_cursor
   DEALLOCATE refresh_cursor

CSDN提问:

如何在sqlserver中实现text字段的特定字符串替换  
   
  就是将text字段里面的所有如“<xxx>”   替换为   “<yyy>”,   这个在sqlserver里操作起来好像很麻烦，请帮忙，谢先！

 

--创建测试数据   
  create   table   t(id   int,var1   text)   
  insert   into   t   select   1,'asdfasdfasdf<xxx>,s,fasdf(xxx),<xxx>a<xxx>a<xxx>asf'   
    
  --执行更新操作   
  declare   @str1   varchar(100),@str2   varchar(100)   
  declare   @i   int,@len   int   
  declare   @ptrval   binary(16)   
  set   @str1='<xxx>'   
  set   @str2='<yyyy>'   
  set   @len=len(@str1)   
    
  SELECT   @i   =   patindex('%'+@str1+'%',var1)-1   from   t   WHERE   id   =   1   
    
  while   @i   >   0   
  begin   
          SELECT   @ptrval   =   TEXTPTR(var1)   FROM   t   WHERE   id   =   1   
          UPDATETEXT   t.var1   @ptrval   @i   @len   @str2     
          SELECT   @i   =   patindex('%'+@str1+'%',var1)-1   from   t   WHERE   id   =   1   
  end   
  GO   
    
  --查看更新结果   
  select   *   from   t   
  /*   
  id     var1   
  --     ------------------------------------------------------------------   
  1       asdfasdfasdf<yyyy>,s,fasdf(xxx),<yyyy>a<yyyy>a<yyyy>asf   
  */   
    
    
  --删除测试数据   
  drop   table   t   
  go

 

 

当text列的数据长度小于8000字节时，可以直接使用replace()函数执行替换操作。