Forgot your password?

Home > About Us > Modern Signal Blog >Replace in a Text Column

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

Testimonials

  • Modern Signal significantly enhanced our site to be more efficient and user-friendly. They provide excellent customer service with timely and cost-effective solutions.

    - Center for Medicare Education

  • This was by far the smoothest website redevelopment I have ever experienced. Modern Signal was a wonderful company to work with and we greatly value our working relationship. 

    - National Association of Student Financial Aid Administrators

  • I love working with Modern Signal! Their CMS is very easy to use and they are incredibly responsive to questions or challenges I bring them.

    - NALP

  • Modern Signal worked with us to understand our needs and figure out what solution would work best for us. Our Lighthouse CMS is perfectly suited to our website goals. When we later needed to modify the CMS, they again took the time to understand exactly what was  needed and then built that functionality rather than delivering a cookie cutter solution.   

    - Ecosystem Investment Partners

  • We wouldn’t have gotten where we are today without your support over the years.  Modern Signal has always been a great partner to us.

    - Kirk Gillis, Managing Director at Zoom Tanzania

  • Modern Signal has a professional staff that was very responsive to our needs during all phases - scoping, developing, implementing and maintaining - of our project.  We have been pleased with their ability to deliver quality work on time and on budget. If given the opportunity, I would work with them again.

    - The National Center for Safe Routes to School

  • I felt as if my company was their only client. They responded to my needs quickly and efficiently despite short turn around time and intense demands.

    - Teaching Strategies, Inc.

  • Modern Signal has been a great partner for us for over the past 10 years.  As our business grew and our needs changed, Modern Signal was able to work with us to adjust our website platform in the ever-changing online world.  Their service and response level has been second to none, and we've been never been happier with our relationship with them.

    - Charm City Run

  • Modern Signal understands our business - from future needs to current limitations - so their solutions are always scalable, solid, and service-oriented.

    - National Association of Home Builders