CODEWITHSUNDEEP
javasqlsql-serverregexsql-update
2787184
2787184

Reputation: 3881

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

Upvotes: 7

Views: 27158

Answers (3)

Jason Clark
Jason Clark

Reputation: 1425

Try to execute this one:

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

Upvotes: 3

2787184
2787184

Reputation: 3881

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

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

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

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

Upvotes: 9

Related Questions