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.

Raging Bull · Accepted Answer · 2014-05-18T06:09:25.890

0

Try this:

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

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

edited May 18 '14 at 06:09

answered May 17 '14 at 16:09

Raging Bull

Fantastic, banging my head on the desk! @Raging_Bull – DGraham May 17 '14 at 16:25
1

Another option is `trim(colname, '"')` – a_horse_with_no_name May 17 '14 at 17:09

score 0 · Answer 2 · edited May 23 '17 at 10:28

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.

2 Answers2