TEXT/NTEXT Find and Replace

--usage exec pr_FindAndReplaceTextDatatype YOURTABLE,ThePrimaryKeyOfTheTable,TheTEXT/NTEXTFieldName,OptionalWHEREStatementTolimitImpact,OldStringToReplace,NewStringToReplaceWith
--usage exec pr_FindAndReplaceTextDatatype 'GMAMEMO','ACTMEMOTBLKEY','TCOMMENT','','{\rtf1','{\rtf2'
--usage exec pr_FindAndReplaceTextDatatype 'GMAMEMO','ACTMEMOTBLKEY','TCOMMENT','WHERE ACTMEMOTBLKEY  BETWEEN 8 AND 75','Coca Cola Classic','Just Coke'

CREATE Procedure pr_FindAndReplaceTextDatatype 
--DECLARE 
        @TableName        varchar(255),
        @PKIDColumnName   varchar(255),
        @TextColumnName   varchar(255),
        @WhereStatement   varchar(1000) = '',
        @OldString        varchar(255),
        @NewString        varchar(255)
AS
  DECLARE
        @sql              varchar(2000),
        @LenOldString     int
  BEGIN
    --Assign variables
    SET @LenOldString=datalength(@OldString)
    --string building..single quotes handled special
    SET @OldString = REPLACE(@OldString,'''','''''')
    SET @NewString = REPLACE(@NewString,'''','''''')
    --initialize row identifier
    SET @sql = '  DECLARE          ' + CHAR(13)
    SET @sql = @sql + '        @LenOldString     int, '       + CHAR(13)
    SET @sql = @sql + '        @WhichPKID        int,  '      + CHAR(13)
    SET @sql = @sql + '        @idx              int, '       + CHAR(13)
    SET @sql = @sql + '        @ptr              binary(16) ' + CHAR(13)
    SET @sql = @sql + '  SET @LenOldString = ' + CONVERT(varchar,@LenOldString) + CHAR(13)
    SET @sql = @sql + '  SELECT TOP 1 @WhichPKID = ' + @PKIDColumnName + ',  ' + CHAR(13)
    SET @sql = @sql + '             @idx      = PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ')-1  ' + CHAR(13)
    SET @sql = @sql + '  FROM ' + @TableName + '  ' + @WhereStatement + ' ' + CHAR(13)
    SET @sql = @sql + '  WHERE PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ') > 0  ' + CHAR(13)
    SET @sql = @sql + ' ' + CHAR(13)
    SET @sql = @sql + '  WHILE @WhichPKID > 0  ' + CHAR(13)
    SET @sql = @sql + '    BEGIN  ' + CHAR(13)
    SET @sql = @sql + '      SELECT @ptr = TEXTPTR(' + @TextColumnName + ')  ' + CHAR(13)
    SET @sql = @sql + '      FROM ' + @TableName + '  ' + CHAR(13)
    SET @sql = @sql + '      WHERE ' + @PKIDColumnName + ' = @WhichPKID  ' + CHAR(13)
    SET @sql = @sql + '      UPDATETEXT ' + @TableName + '.' + @TextColumnName + ' @ptr @idx ' +  convert(varchar,@LenOldString) + ''''+ @NewString + '''  ' + CHAR(13)
    SET @sql = @sql + '      SET @WhichPKID = 0  ' + CHAR(13)
    SET @sql = @sql + '      SELECT TOP 1 @WhichPKID = ' + @PKIDColumnName + ', @idx = PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ')-1  ' + CHAR(13)
    SET @sql = @sql + '      FROM ' + @TableName + '  ' + @WhereStatement + ' ' + CHAR(13)
    SET @sql = @sql + '      WHERE ' + @PKIDColumnName + ' > @WhichPKID  ' + CHAR(13)
    SET @sql = @sql + '        AND PATINDEX(''%' + @OldString + '%'',' + @TextColumnName + ') > 0  ' + CHAR(13)
    SET @sql = @sql + '   END  ' + CHAR(13)
    PRINT @sql
    EXEC (@sql)
  END --PROC