SQL Server find and replace in TEXT field

Question

I have a database in SQL Server 2005 that was brought up from SQL Server 2000 and is still using TEXT type fields instead of varchar(max).

I need to find and replace a string of characters in the text field but all of the examples of how to do this that I have found don't seem like they would work for me. It seems the UPDATETEXT command requires that the two parameters "insert_offset" and "delete_length" be set explicitly but the string i am searching for could show up in the text at any point or even at several points in the same cell. My understanding of these two parameters is that the string im searching for will always be in the same place, so that insert_offset is the number of spaces into the text that the UPDATETEXT command will start replacing text.

Example: Need to find: <u> and Replace it with: <u>

Text field example:

*Everyone in the room was <b>&lt;u&gt;tired&lt;/u&gt;.</b><br>Then they woke <b>&lt;u&gt;up&lt;/u&gt;. 

Can anyone help me out with this? THANKS!

Answer 1

I finally figured it out. It was buried in the comments to the article jfrobishow published. Thank you SO much.

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 I wrote 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 

Note:that this may truncate the size of you dbfield , but if is a long text column make it nvarchar(max) and you should not get none truncation!