亂馬客

前言

有朋友問我說要如何REPLACE NTEXT欄位的資料，直覺就說使用REPLACE呀! 可是實際來試一下卻會有的”Argument data type ntext is invalid for argument 1 of replace function.”錯誤!

研究

在測試前，先建立要測試的資料表及資料吧!

--NText欄位的多個字元Replace
--建立測試的Table
IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[TEST1]') AND type in (N'U'))
DROP TABLE [dbo].[TEST1]
GO

CREATE TABLE TEST1
(
    C1 INT IDENTITY(1,1) NOT NULL
    ,C2 NTEXT
)
GO

--加入測試的資料
INSERT INTO TEST1 VALUES(  '1111')
INSERT INTO TEST1 VALUES(  '2222')
INSERT INTO TEST1 VALUES(  '3333')
INSERT INTO TEST1 VALUES(  '＜！－－123－－＞')
INSERT INTO TEST1 VALUES(  '＜－－123－－＞')
INSERT INTO TEST1 VALUES( 'ＡＢＣＤＥＦ')
GO

原來REPLACE的參數要的是NVARCHAR呀! 那最快的方式就是直接REPLACE成NVARCHAR再UPDATE吧! 在SQL 2005後的NVARCHAR(MAX)可說是跟"超人力霸王MAX"一樣超利害!請參考以下的SQL!

SELECT * FROM TEST1--方法1
--如果資料庫是SQL2005以上(含SQL2005)，
--可以將nText欄位Convert成NVarchar(MAX)，再Replace
--設定要Replace的字串對應表(全形轉半形)
DECLARE @CHK_LIST NVARCHAR(MAX), @REPLACE_LIST NVARCHAR(MAX)
SET @CHK_LIST = '＜！－－＞ＡＢＣＤＥＦＧ'
SET @REPLACE_LIST = '<!-->ABCDEFG'


DECLARE @CHK_CHAR NCHAR(1), @CHK_LEN INT
DECLARE @IDX INT, @REP_CHAR NCHAR(1)
SET @CHK_LEN = LEN(@CHK_LIST)
SET @IDX = @CHK_LEN
WHILE @IDX > 0
BEGIN
    SET @CHK_CHAR = SUBSTRING(@CHK_LIST, @IDX, 1)
    SET @REP_CHAR = SUBSTRING(@REPLACE_LIST, @IDX, 1)
    --先轉成NVARCHAR再REPLACE
    UPDATE TEST1 SET C2 =  REPLACE(CAST(C2 AS NVARCHAR(MAX)), @CHK_CHAR, @REP_CHAR ) 
    SET @IDX = @IDX -1
END

SELECT * FROM TEST1
GO

如果是SQL 2000以的話，就只好使用UPDATETEXT更新啦! 不過使用UPDATETEXT一次只能UPDATE一筆資料的一個地方哦!

SELECT * FROM TEST1--方法2
--如果資料庫是SQL2000的話，就只好使用UPDATEText
--參考:http://www.kodyaz.com/articles/update-text-column-using-updatetext.aspx
--設定要Replace的字串對應表(全形轉半形)
DECLARE @CHK_LIST NVARCHAR(MAX), @REPLACE_LIST NVARCHAR(MAX)
SET @CHK_LIST = '＜！－－＞ＡＢＣＤＥＦＧ'
SET @REPLACE_LIST = '<!-->ABCDEFG'

--記錄指標及更新的位罝
DECLARE @DEST_TXT_PTR BINARY(16)
DECLARE @INSERT_OFFSET INT

DECLARE @CHK_CHAR NCHAR(1), @CHK_LEN INT, @IDX INT, @REP_CHAR NCHAR(1)
SET @CHK_LEN = LEN(@CHK_LIST)
SET @IDX = @CHK_LEN
WHILE @IDX > 0
BEGIN
    SET @CHK_CHAR = SUBSTRING(@CHK_LIST, @IDX, 1)
    SET @REP_CHAR = SUBSTRING(@REPLACE_LIST, @IDX, 1)
    --一筆筆去更新
    DECLARE TEST1_CURSOR CURSOR FOR 
    SELECT C1
    FROM TEST1

    OPEN TEST1_CURSOR

    DECLARE @TABPK INT
    FETCH NEXT FROM TEST1_CURSOR 
    INTO @TABPK

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --使用UPDATE TEXT來更新NTEXT欄位的資料
        --取得要更新的POINT及要UPDATE的ADDRESS
        SELECT @DEST_TXT_PTR = TEXTPTR(C2) 
            , @INSERT_OFFSET = PATINDEX(@CHK_CHAR, C2) - 1
            FROM TEST1 
            WHERE C1 = @TABPK
        PRINT @INSERT_OFFSET
        --如果有找到的話，就再找下去
        WHILE @INSERT_OFFSET > -1
        BEGIN
            UPDATETEXT TEST1.C2 @DEST_TXT_PTR @INSERT_OFFSET 2 @REP_CHAR;
            SELECT
                @INSERT_OFFSET = PATINDEX(@CHK_CHAR, C2) - 1
            FROM TEST1
            WHERE C1 = @TABPK
        END

        FETCH NEXT FROM TEST1_CURSOR 
        INTO @TABPK
    END 
    CLOSE TEST1_CURSOR
    DEALLOCATE TEST1_CURSOR

    UPDATE TEST1 SET C2 =  REPLACE(CAST(C2 AS NVARCHAR(MAX)), @CHK_CHAR, @REP_CHAR ) 
    SET @IDX = @IDX -1
END

SELECT * FROM TEST1

 

結論

使用UpdateText來更新資料顯的更麻煩一點，要更新還要用CURSOR來搞! 而且MS的Help上還說，未來可能不Support! 所以建議還是多使用NVARCHAR(MAX)! MAX, MAX, MAX!

參考資料

Update Text, NText Columns using UPDATETEXT Command in MS SQL Server

SQL Server 2008 数据类型－varchar(MAX)、nvarchar(MAX) 到底有多大？

測試範例

NTEXT_REPLACE.rar

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^