Remove " " from values

Question

In PostgreSQL a column contains values such as:

"Sample1"
"Sample2"

Is there an efficient way to go through each record removing the " " marks?

This seems like a relatively straightforward operation, but I am baffled.

Answer 1

Try this:

UPDATE TableName
SET ColName = REPLACE(ColName, '"', '');
WHERE ColName SIMILAR TO '%"%'

You can use LIKE operator as well instead of SIMILAR TO.

Answer 2

Since you asked for an efficient way:

UPDATE tbl
SET    col = translate(col, '"', '')
WHERE  col LIKE '%"%';

• Never use SIMILAR TO, its an utterly pointless operator, only included in Postgres for standard compliance. There is hope the standard will include a regular expression match (like ~ in Postgres) instead in the future.

• For replacing single characters, translate() is simpler and faster than replace().

• If you only want to replace leading and / or trailing characters, use trim() / ltrim() / rtrim() instead. And also change to WHERE col LIKE '"%' OR col LIKE '%"' respectively.