Easy find and replace in SQL Text fields

As you may know, you can only use the REPLACE SQL function on varchar or char fields, which becomes a problem when you have a text field to update (as we often do). In my case I had a text field with values up to 14,000 bytes, far too long to do a CAST to varchar (with a maximum of 8,000 bytes).

The traditional solution to this problem is to either use the UPDATETEXT function in a cursor, with lots of nasty code, or to whip up a small throwaway .NET application to iterate through the database records using the VB.NET Replace function.

SQL Server 2005 introduces the idea of large-value data types, which allows you to specify a data type that can store values up to 2^31 (2,147,483,648) bytes of character, binary or Unicode data (equal to the String data type in .NET). The importance of this change is obvious from the help text on these:

“The built-in string functions that can operate on character and binary data are enhanced to support large-value data types as arguments”

The syntax for the type declaration is:

• varchar(max)
• nvarchar(max)
• varbinary(max)

This allowed me to write the following update statement:

UPDATE ItemContent
SET ItemContent = CAST(REPLACE(CAST(ItemContent AS VARCHAR(max)), '#ff9900', '#8b0b04') AS TEXT)

Much easier than messing around with textpointers and offset values with the UPDATETEXT function!!