Monday, August 22, 2005 11:05 AMHi!
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.
Monday, August 22, 2005 1:49 PMYou might be able to use PATINDEX along with UPDATETEXT to replace all occurances in your TEXT column. Have a look here:
However, I think it is more effective to do such things client-side.
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
Monday, August 22, 2005 2:02 PMThanks Frank. This will do the job.