There are ridiculously complicated ways to overcome this but the easieast solution I was able to find around is the following:
UPDATE myTable
SET myField = REPLACE(SUBSTRING(myField, 1, DATALENGTH(myField)), 'string2replace', 'replacementString')SUBSTRING is returning VARCHAR so this way it works like charme.
I HATE SQL but I am getting to know more than I would like about it.
UPDATE:
as pointed out by people in the comments, the above will truncate to 8000 characters. This wasn't a concern for me at all because I knew my strings where pretty short. If you're worried about truncation you can use a CAST (check comments for more details).
12 comments:
Fantastic...Thanks so much. This worked like a charm.
It seems to truncate the text to 8000 characters
I believe SUBSTRING converts to varchar(max) which is indeed 8000 ... :0
You can use the CAST keyword to cast the text field to Varchar(8000) for instance like so:
CAST(myfield AS Varchar(8000)
Excellent! I was headed down that ridiculously complicated path. You saved me serious time.
Handy trick saved me a bunch of time.
Nice one - better than the 50 line alternatives online!
Thanks -- you saved me a lot of time on this.
Incredibly complete and extremely good advices on how to replace on text field.Thanks and great job
Thanks...you saved my hours...
Are you guys serious? This will truncate your text if it's longer than 8000 characters!
Using SUBSTRING( myField, 1, DATALENGTH(myField)) will truncate to 8000 chars. If you use CAST( myField as varchar(max)) it does not.
Post a Comment