How to replace & to & in SQL?

Question

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;]*','&');

Answer 1

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

This will first replace "&" with "&", then replace all "amp;" with "" (empty string).

Answer 2

Try to execute this one:

UPDATE Tablename
SET columnname = REPLACE(columnname, '&', '&')
WHERE columnname LIKE '%&%'