Replace strings in Text column

Question

Hi!

I would like to replace some strings (for instance 'mystring1' with 'mystring2') in a column of datatype Text. Replace function does not work with Text columns. The following works:

update mytable set myfield=replace(convert(varchar(8000), myfield),'mystring1','mystring2')

but it truncates data the exceed the 8000 bytes. Ofcourse I have some rows containing more than 8000 bytes in that field, that's why it is set a Text.

Any ideas?

Answer 1

You might be able to use PATINDEX along with UPDATETEXT to replace all occurances in your TEXT column. Have a look here:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://www.aspfaq.com/show.asp?id=2445

However, I think it is more effective to do such things client-side.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Answer 2

Thanks Frank. This will do the job.