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

not my solution, but maybe this code will help you

http://blogs.x2line.com/al/archive/2008/05/03/3417.aspx

Answer 2

These articles seems to address the issue:

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

Answer 3

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