Search and Replace a text in SQL Server Table Column

If you have a Table which has a Column of type char or varchar or even text, and you want to change a portion of text in it for example a path.In this case you need to change the same text with new one in every records.Here is the SQL Statement that do this:

   1:  UPDATE [TableName]

   2:  SET [ColumnName] = Replace([ColumnName],'OldText','NewText')

Here is the whole response that led me to the solution:

quote:Originally posted by fredclown

If you use SQL 2005 you can use replace with a text type. All you have
to do is the below ...


field = replace(cast(field as varchar(max)),'string' ,'replacement')

Easy as pie.

Two thumbs up to Fredclown!!! command work like a charm for me as well. This is what Iwrote my Update statement to Find and Replace in a Text field in SQL server 2005 database

UPDATE TableName
SET DBTextField = REPLACE(CAST(DBTextField AS varchar(MAX)), 'SearchText', 'ReplaceText')
FROM TableName
WHERE CHARINDEX('SearchText',CAST(DBTextField as varchar(MAX)))>0