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:
UPDATE ItemContentMuch easier than messing around with textpointers and offset values with the UPDATETEXT function!!
SET ItemContent = CAST(REPLACE(CAST(ItemContent AS VARCHAR(max)), '#ff9900', '#8b0b04') AS TEXT)