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()函数执行替换操作。