SQL Server Replace Text within a long string

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 [&]

update MyTable set MyColOne= replace(cast(MyColOne as varchar(max)),'and' ,'&')
Use this if you only want to replace characters for a certain record.
update MyTable set MyColOne= replace(cast(MyColOne as varchar(max)),'and' ,'&') where MyColID=1