Tuesday, May 25, 2010

Search and Replace in text column

You can not use T-SQL Replace function directly on a text field.

There are more complex ways to do this but if your filed size is < 8000 ( or <4000 for nvarchar), simple sql below may serve the purpose

UPDATE myTableSET set myField = REPLACE(SUBSTRING(myField, 1, DATALENGTH(myField)), 'searchText', 'replacementText') where DATALENGTH(myField) < 8000