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()函式執行替換操作。