This simple script allows you to replace text or characters within a string, regardless of its size, in SQL Server.
We are going to replace every instance of the word, [and] with the [and] symbol [&]
[SQL Server - Replace Text within a long string]
update MyTable set MyColOne= replace(cast(MyColOne as varchar(max)),'and' ,'&')
Use this if you only want to replace characters for a certain record.
[SQL Server - Replace Text within a long string]
update MyTable set MyColOne= replace(cast(MyColOne as varchar(max)),'and' ,'&') where MyColID=1