数据库ntext,text字段的内容批量替换储存过程.

 
-- select * from  dbo.cms_detail
if     exists ( select   1   from  tempdb.dbo.sysobjects  where  name  like   ' #cms_detailtmp% ' )     
   DROP   TABLE  #cms_detailtmp
CREATE   TABLE  #cms_detailtmp
 
( 
 
    ID      INT   IDENTITY ( 1 , 1 )  PRIMARY   KEY ,
 
    A       TEXT
 
)
 
 
set  nocount  on
DECLARE   @sTableName  SYSNAME
 
DECLARE   @sFieLd  SYSNAME
 
DECLARE   @sSrc   VARCHAR ( 20 )
 
DECLARE   @sDest   VARCHAR ( 20 )
 
 
SET   @sTableName   =   ' cms_detail '                       --  待更新的表的名称
 
SET   @sField   =   ' content '                          --  待更新的字段的名称
 
SET   @sSrc   =   ' .MEGAJOY.COM '                         --  要被替换的字符串（源字符串）
 
SET   @sDest   =   ' .JOY.CN '                              --  进行替换的目标字符串
 
 
 
DECLARE   @PTRVAL   BINARY ( 16 )                        --  指向当前文本字段的指针
 
DECLARE   @VAL   VARCHAR ( 8000 )                        --  当前读取的文本字段的部分
 
DECLARE   @PREVVAL   VARCHAR ( 8000 )                    --  由于源字符串跨界产生的源字符串前缀回退部分
 
DECLARE   @VALLEN   INT                                --  当前读取的文本字段的长度
 
DECLARE   @STATEMENT   NVARCHAR ( 512 )                   --  进行执行sp_executesql的SQL语句部分
 
DECLARE   @ISNTEXT   BIT                            --  是否NTEXT字段类型0: TEXT   1: NTEXT
 
DECLARE   @READPTR   INT                            --  当前TEXT或者NTEXT字段已经读取或者处理的字符偏移量
 
DECLARE   @READLEN   INT                            --  当次TEXT或者NTEXT字段读取的长度
 
DECLARE   @SRCLEN   INT                                --  源字符串的长度
 
DECLARE   @DESTLEN   INT                            --  目标字符串的长度
 
DECLARE   @VALBINARY   VARBINARY ( 8000 )                --  本次读取的文本字段的BINARY表示，用来识别是否已经截断了一个DBCS字符
 
DECLARE   @VALBINARYLEN   INT                          --  本次读取的文本字段的BINARY表示的长度
 
DECLARE   @HALFDBCS   INT                              --  当前识别的是否DBCS的一部分
 
 
 
 
 
SET   @SRCLEN    =   LEN (  @sSrc  )
 
SET   @DESTLEN   =   LEN (  @sDest  )
 
 
 
DECLARE  csrTextUpdate  CURSOR  LOCAL  FOR
 
     SELECT   TEXTPTR (content), 
 
           DATALENGTH (content)
 
           FROM  cms_detail
 
     FOR   UPDATE   OF  content
 
 
 
OPEN  csrTextUpdate
 
declare   @count   int
select   @count = count ( * )  from  cms_detail
print   ' 共 ' + cast ( @count   as   varchar );
set   @count   = 1 ;
 
 
--  xtype = 35 为TEXT  xtype = 99 为NTEXT
 
IF   EXISTS (  SELECT   *   FROM  SYSCOLUMNS  WHERE  ID  =   OBJECT_ID ( ' cms_detail ' )  AND  NAME  =   @sField   AND  XTYPE  =   35  )
 
     SET   @ISNTEXT   =   0
 
ELSE
 
     SET   @ISNTEXT   =   1
 
 
 
FETCH  csrTextUpdate  INTO   @PTRVAL ,  @VALLEN
 
WHILE   @@FETCH_STATUS   =   0
 
BEGIN
     print   ' 现在是第 ' + cast ( @count   as   varchar );
     set   @count = @count + 1 ;
     IF   @VALLEN   >   8000
 
     BEGIN
 
    
 
        SET   @VALLEN   =   CASE   WHEN   @ISNTEXT   =   1   THEN   @VALLEN   /   2   ELSE   @VALLEN   END
 
        SET   @READPTR   =   0
 
        SET   @READLEN   =   CASE   WHEN   @VALLEN   >   @READPTR   +   2000   THEN   2000   ELSE   @VALLEN   -   @READPTR   END            
 
 
 
        TRUNCATE   TABLE  #cms_detailtmp
 
 
 
        --  分拆TEXT字段到#cms_detailtmp表
 
        WHILE   @READPTR   <   @VALLEN
 
        BEGIN
 
           
 
            SET   @READLEN   =   CASE   WHEN   @VALLEN   >   @READPTR   +   2000   THEN   2000   ELSE   @VALLEN   -   @READPTR   END
 
            SET   @STATEMENT   =  N ' READTEXT cms_detail.content @PTRVAL  '  
 
                          +   CONVERT (  NVARCHAR ,  @READPTR  )  +   '   '  
 
                          +   CONVERT (  NVARCHAR ,  @READLEN  )
 
           
 
            SET   @READPTR   =   @READPTR   +   @READLEN
 
 
 
            INSERT   INTO  #cms_detailtmp
 
            EXEC  sp_executesql  @STATEMENT ,
 
                           N ' @PTRVAL BINARY(16) ' ,
 
                            @PTRVAL
 

            --  判断最后一个字符是否为中文字符，如果是，则需要进行回退操作
 
           
 
            IF   @@ROWCOUNT   =   1   AND   @ISNTEXT   =   0
 
            BEGIN              
 
               SELECT   @VALBINARY   =   CONVERT (  VARBINARY ( 8000 ),  CONVERT (  VARCHAR ( 8000 ), A ))
 
                      FROM  #cms_detailtmp  WHERE  ID  =   @@IDENTITY
 
               SET   @VALBINARYLEN   =   DATALENGTH ( @VALBINARY )
 
 
 
               IF   @VALBINARYLEN   =   @READLEN
 
               BEGIN                 
 
                   SET   @HALFDBCS   =   0
 
               END
 
               ELSE
 
               BEGIN
 
                   SET   @HALFDBCS   =   1
 
               END
 
              
 
               IF   @HALFDBCS   =   1
 
               BEGIN
 
                   --  回退一个字符串
 
                   UPDATE  #cms_detailtmp
 
                      SET  A  =   CONVERT (  VARCHAR ( 8000 ),  SUBSTRING (  @VALBINARY ,  1 ,  @READLEN   -   1  ) )
 
                      WHERE  ID  =   @@IDENTITY
 
                   SET   @READPTR   =   @READPTR   -   1
 
               END
 
            END
 
        END
 
 
 
        --  进行分批替换,如果有需要被替换的字符串跨段的情况需要进行跨段处理
 
        DECLARE  csrTmp  CURSOR  LOCAL  FOR
 
            SELECT  A  FROM  #cms_detailtmp
 
            FOR   UPDATE   OF  A
 
        OPEN  csrTmp
 
        FETCH  csrTmp  INTO   @VAL
 
 
 
        SET   @PREVVAL   =   ''
 
        WHILE   @@FETCH_STATUS   =   0
 
        BEGIN
 
            SET   @VAL   =   REPLACE (  @PREVVAL   +   @VAL ,  @sSrc ,  @sDest  )
 
           
 
            SET   @VALLEN   =   LEN ( @VAL )
 
 
 
            SET   @READPTR   =   CASE   WHEN   @SRCLEN   >   @VALLEN   THEN   @VALLEN   ELSE   @SRCLEN   END
 
           
 
            SET   @PREVVAL   =   ''
 
            
 
            WHILE   @READPTR   >   0
 
            BEGIN
 
              
 
               IF   RIGHT (  @VAL ,  @READPTR  )  =   LEFT (  @sSrc ,  @READPTR  )
 
               BEGIN
 
                  
 
                   --  找到前缀，将当前的@VAL截断一部分
 
                   UPDATE  #cms_detailtmp
 
                      SET   [ A ]   =   LEFT (  @VAL ,  @VALLEN   -   @READPTR  )
 
                      WHERE   CURRENT   OF  csrTmp
 
 
 
                   SET   @PREVVAL   =   RIGHT (  @VAL ,  @READPTR  )
 
                   BREAK
 
               END
 
 
 
               SET   @READPTR   =   @READPTR   -   1
 
            END
 
 
 
            IF   @PREVVAL   =   ''
 
            BEGIN
 
               UPDATE  #cms_detailtmp
 
                   SET   [ A ]   =   @VAL
 
                   WHERE   CURRENT   OF  csrTmp
 
            END
 
 
 
            FETCH  csrTmp  INTO   @VAL
 
        END
 
        DEALLOCATE  csrTmp
 
 
 
 
 
        --  更新TEXT字段
 
        UPDATETEXT  cms_detail.content  @PTRVAL   0   NULL   ''
 
 
 
        DECLARE  csrUpdateText  CURSOR  LOCAL  FOR  
 
            SELECT  A  FROM  #cms_detailtmp
 
    
 
        OPEN  csrUpdateText
 
        FETCH  csrUpdateText  INTO   @VAL
 
        WHILE   @@FETCH_STATUS   =   0
 
        BEGIN
 
           
 
            UPDATETEXT  cms_detail.content  @PTRVAL   NULL   0   @VAL
 
            FETCH  csrUpdateText  INTO   @VAL
 
        END
 
        DEALLOCATE  csrUpdateText
 
    
 
     END
 
     ELSE
 
     BEGIN
 
        --  如果是NTEXT的类型，字符串的长度为字节长度的/2
 
        UPDATE  cms_detail 
 
            SET  content  =   REPLACE (  CONVERT (  VARCHAR ( 8000 ), content),  @sSrc ,  @sDest  )
 
            WHERE   CURRENT   OF  csrTextUpdate
 
     END
 
                
 
     FETCH  csrTextUpdate  INTO   @PTRVAL ,  @VALLEN
 
END
 
 
 
DEALLOCATE  csrTextUpdate
 
GO