Search and Replace in a Text Column

SQL Server has a replace function, but it doesn't work on "text" columns.  SQL Server seems to address this with a new varchar(max) datatype, but those of us still using SQL Server 2000 still need a solution.  The following stored procedure should do the trick.  The seed of the idea for this came from the following page:
http://www.sqlteam.com/article/search-and-replace-in-a-text-column

My version puts the code in a stored procedure, replaces all instances of the string, escapes any wildcard characters that might be in the string, and also prints out the number of replacements that are done.  It is limited in that it won't work if the new text contains the old text, because that would cause an infinite loop, but maybe we'll be using varchar(max) before we need to do that!

- David (dave@modernsignal.com)

Usage:
EXEC usp_utility_replacetext 'Content','htmlContent','oldtext','newtext'

CREATE PROCEDURE dbo.usp_utility_ReplaceText
    @TableName VARCHAR(32),
    @ColumnName VARCHAR(32),
    @otxt VARCHAR(100),
    @ntxt VARCHAR(100)
AS
DECLARE @num INT
DECLARE @sql NVARCHAR(4000)
DECLARE @parms NVARCHAR(4000)
DECLARE @invalidChars VARCHAR(20)

SET @invalidChars = '%[^a-z1-9_@#$]%'

IF PATINDEX(@invalidChars, @TableName) > 0
BEGIN
    PRINT 'Invalid character in table name.'
    RETURN
END

IF PATINDEX(@invalidChars, @ColumnName) > 0
BEGIN
    PRINT 'Invalid character in column name.'
    RETURN
END

IF CHARINDEX(@ntxt, @otxt) > 0
BEGIN
    PRINT 'Sorry, the new text cannot contain the old text.'
    RETURN
END

SET @sql = N'
    DECLARE @txtlen INT
    DECLARE @ptr BINARY(16)
    DECLARE @pos INT
    DECLARE @otxtPat VARCHAR(100)
    DECLARE @more bit

    SET @txtlen = LEN(@otxtIN)
    SET @otxtPat = @otxtIN
    SET @otxtPat = REPLACE(@otxtPat,''%'',''[%]'')
    SET @otxtPat = REPLACE(@otxtPat,''_'',''[_]'')
    SET @otxtPat = ''%'' + @otxtPat + ''%''
    SET @numOUT = 0

    WHILE EXISTS (
        SELECT * FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
    )
    BEGIN
        DECLARE curs CURSOR local fast_forward
        FOR SELECT
            TEXTPTR(' + @ColumnName + '),
            PATINDEX(@otxtPat,
            ' + @ColumnName + ') - 1
        FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
    OPEN curs
   
    FETCH NEXT FROM curs INTO @ptr, @pos
    WHILE @@fetch_status = 0
    BEGIN
        UPDATETEXT
        ' + @TableName + '.' + @ColumnName + '
        @ptr @pos @txtlen @ntxtIN
        SET @numOUT = @numOUT + 1
        FETCH NEXT FROM curs INTO @ptr, @pos
    END
    CLOSE curs
    DEALLOCATE curs
    END'

SET @parms = N'@otxtIN VARCHAR(100),@ntxtIN VARCHAR(100),@numOUT INT OUTPUT'
EXEC sp_executesql @sql, @parms,
    @otxtIN = @otxt,
    @ntxtIN = @ntxt,
    @numOUT = @num OUTPUT
PRINT CONVERT(VARCHAR(32), @num) + ' occurrences replaced.'
GO