[MSSQL] 특정 테이블의 NTEXT 값을 치환하는 프로시저

안녕하세요. 오랜만에 Coders 카테고리에 글 씁니다.

오늘은 제목 그대로의 내용을 작성하는데요, MSSQL 2005 이상의 버전에서는 물론 NVARCHAR(MAX) 등을 사용하여 문자열 함수인 REPLACE 를 사용할 수 있지만, 그 이전 버전에서 TEXT, NTEXT 의 값을 치환하는데에는 좀 애를 먹습니다.

인터넷 찾아보면 여러가지 예제가 있는데요, 저는 프로시저로 구현해 보았습니다.

인터넷 게시판 등에서 참조하던 아이콘 이미지등의 서버 URL 이 바뀌었다든지 할 때 유용할 수 있겠습니다.

단, 실행 시간은 좀 오래 걸리며, 백업을 필수. (사실 저도 백업 안 하다가 날려먹은 적이 있지만 뭐.)

각설하고, 나갑니다.

IF OBJECT_ID('DBO.SP_REPLACETEXT', 'P') IS NOT NULL
    DROP PROCEDURE DBO.SP_REPLACETEXT;
GO

CREATE PROCEDURE DBO.SP_REPLACETEXT
(
    @TABLE_NAME     NVARCHAR(256),
    @COLUMN_NAME    NVARCHAR(256),
    @FR_STR         NVARCHAR(1000),
    @TO_STR         NVARCHAR(1000)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @P_TXTPTR         BINARY(16),
            @P_START          INT,
            @P_LEN            INT,
            @P_COUNT          INT,
            @P_COUNT_QUERY    NVARCHAR(4000),
            @P_COUNT_PARAM    NVARCHAR(4000),
            @P_CONVERT_COUNT  INT,
            @P_QUERY          NVARCHAR(4000),
            @P_PARAM          NVARCHAR(4000);

    SET @P_CONVERT_COUNT = 0;
    SET @P_LEN = LEN(@TO_STR);

    SET @P_COUNT_QUERY = 'SELECT @P_COUNT2 = COUNT(1) FROM '+@TABLE_NAME+
                         ' WHERE '+@COLUMN_NAME+' LIKE ''%'' + '''+@FR_STR+''' + ''%''';
    SET @P_COUNT_PARAM = '@P_COUNT2 INT OUTPUT';

    WHILE 1 = 1
    BEGIN
        EXECUTE SP_EXECUTESQL @P_COUNT_QUERY, @P_COUNT_PARAM, @P_COUNT2=@P_COUNT OUTPUT;

        IF @P_COUNT < 1
            BREAK;

        SET @P_QUERY =
        'DECLARE CUR_FIND CURSOR FOR'+
        ' SELECT TEXTPTR('+@COLUMN_NAME+') AS TXTPTR,'+
        '        PATINDEX(''%'' + '''+@FR_STR+''' + ''%'', '+@COLUMN_NAME+') - 1 AS START_POS'+
        '   FROM '+@TABLE_NAME+
        '  WHERE '+@COLUMN_NAME+' LIKE ''%'' + '''+@FR_STR+''' + ''%''';

        EXEC ( @P_QUERY );

        OPEN CUR_FIND;

        WHILE 1 = 1
        BEGIN
            FETCH NEXT FROM CUR_FIND INTO @P_TXTPTR, @P_START

            IF @@FETCH_STATUS <> 0
                BREAK;

            SET @P_QUERY = 'UPDATETEXT '+@TABLE_NAME+'.'+@COLUMN_NAME+
                           ' @P_TXTPTR2 @P_START2 @P_LEN2 '''+@TO_STR+'''';
            SET @P_PARAM = '@P_TXTPTR2 BINARY(16), @P_START2 INT, @P_LEN2 INT';
            EXECUTE SP_EXECUTESQL @P_QUERY, @P_PARAM,
                    @P_TXTPTR2=@P_TXTPTR, @P_START2=@P_START, @P_LEN2=@P_LEN;

            SET @P_CONVERT_COUNT = @P_CONVERT_COUNT + 1;
        END;

        CLOSE CUR_FIND;
        DEALLOCATE CUR_FIND;
    END;

    SET @P_QUERY = '테이블.컬럼 ['+@TABLE_NAME+'].['+@COLUMN_NAME+'] 에 대해 '+
                   CONVERT(NVARCHAR, @P_CONVERT_COUNT)+'개 값 변환됨';
    PRINT @P_QUERY;

    SET NOCOUNT OFF;
END
GO

-- 예제, www.daum.com/top_img.gif 라는 이미지를 사용하다가
-- www.naver.net/top_img2.png 라는 이미지로 바꿀 경우
SP_REPLACETEXT 'TBL_BBS', 'CONTENT',
               'www.daum.com/top_img.gif', 'www.naver.net/top_img2.png'
GO

결과는 이런 식으로 나옵니다.
테이블.컬럼 [TBL_BBS].[CONTENT] 에 대해 2775개 값 변환됨