Tuesday, September 09, 2008

SQL: Replace strings for TEXT datatype

There might be a simpler way to do a replace of all occurences of a string in a SQL TEXT field, but this is the solution I came up with way back when. I needed it again tonight so I figured I might as well pin it to the interwebs.

-- UPDATE [TableToUpdate]
-- SET [Column] = cast(replace(cast([Column] as varchar(max)),'[StringToReplace]','[ReplacementText]') as text)
-- WHERE [Column] like '%[StringToReplace]%'


  1. Yeah, the whole CAST and replace is a nice trick I learned from those damn SQL injectors! :-(

  2. Thanks very much.

  3. varchar(max) doesn't work on SQL Server versions before 2005. If you're using an edition before 2005, use varchar(8000) if you need the max.