Search and Replace in a TEXT(NTEXT)

MSSQL: Search and Replace in a TEXT(NTEXT) columnIt’s been a while since I’ve posted anything SQL related. So… Sometimes we need to search and replace a text value in the entire table. The column in question is of TEXT or NTEXT datatype. T-SQL REPLACE function does not work with TEXT/NTEXT datatype.

Instead we have to use several other functions:

•UPDATETEXT – Updates an existing text, ntext, or image field.
•TEXTPTR – Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.
•PATINDEX – Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

A while ago I wrote a small utility procedure which uses above functions to implement Search and Replace functionality.

ALTER PROC dbo.SearchAndReplace 
(
     @FindString    NVARCHAR(100)
    ,@ReplaceString NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TextPointer VARBINARY(16) 
    DECLARE @DeleteLength INT 
    DECLARE @OffSet INT 

    SELECT @TextPointer = TEXTPTR([MY_TEXT_COLUMN])
      FROM [MY_TABLE]

    SET @DeleteLength = LEN(@FindString) 
    SET @OffSet = 0
    SET @FindString = '%' + @FindString + '%'

    WHILE (SELECT COUNT(*)
             FROM [MY_TABLE]
            WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0) > 0
    BEGIN 
        SELECT @OffSet = PATINDEX(@FindString, [MY_TEXT_COLUMN]) - 1
          FROM [MY_TABLE]
         WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0

        UPDATETEXT [MY_TABLE].[MY_TEXT_COLUMN]
            @TextPointer
            @OffSet
            @DeleteLength
            @ReplaceString
    END

    SET NOCOUNT OFF
END

发表评论