Wednesday, August 22, 2007

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!!

No comments:

Post a Comment