Using REPLACE in an UPDATE statement

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
Normally, doing a search and replace in SQL is not radically difficult. You basically do an update using the replace() function. For example:

  Update Product
  Set Description = replace(Description, 'old text is this', 'new text will be this')    
  where Description like '%old text is this%' 

[ad#post]
However, if the Description column in your table is a ntext field, the above code is going to error out. The correct way around this is to cast() the column as a maximum-sized varchar() type. So the above will now look like the following:

    Update Product   
    Set Description = replace(cast(Description as varchar(8000)), 'old text is this', 'new text will be this')   
    where Description like ('%old text is this%') 

Thanks

Leave a Reply