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]%'

4 comments:

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

    ReplyDelete
  2. Thanks very much.

    ReplyDelete
  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.

    ReplyDelete