SQL Server : replace "&" to character entities (&)

I have this sample table:

Table1

Column1
-------
Hi&Hello
Hello & Hi
Snacks & Drinks
   Hello World  

Question: in SQL Server, how can I replace all the & into a character entities (&) without affecting the existing character entities?

Thanks!

1. If you really want to replace it in your database, you can try running

    UPDATE Table1 SET Column1 = REPLACE(Column1, '&', '&');

2. I suppose you want to do this because you want to display data on the site exactly the same way in your database. So I suggest you to escape when you display it (in the application side) since it is not easy to maintain when you have lots of & or   in your database.

For example:

In php, you can use htmlspecialchars();

In java, you can import static org.apache.commons.lang.StringEscapeUtils.escapeHtml; and then use escapeHtml();

In ruby on rails, you can use HTMLEntities.new.encode(); (If you use rails3 or newer version, escaping should be done by default.)