How to replace & to & in SQL?

I have some bad data in my database table. I want to replace all & or & or &amp or &amp to & only.

In java it is working fine. how to do in SQL?

 

Java:

String[] names = new String[] { "Ravi Suthar", "Ravi & Suthar", "Ravi & Suthar", "Ravi & Suthar", "Ravi & Suthar" }; for (String name : names) { System.out.println(name.replaceAll("&[amp;]*", "&")); }

 

SQL:

UPDATE tablename SET columnname=REPLACE(columnname,'&[amp;]*','&');

 

 

 

 

 

Following sql will replace & or & or &amp or &amp or its sequence to &

UPDATE tablename SET columnname = REPLACE(REPLACE(columnname, '&', '&'), 'amp;', '');

or

UPDATE tablename SET columnname = REPLACE(columnname , 'amp;', '')