CODEWITHSUNDEEP
sqlpostgresql
user4458270
user4458270

Reputation:

SQL how to replace multiple characters in a single field of the same table with single query

I am trying to replace a particular character with another in my Postgres database using SQL. I am able to do that using two queries. I am not able to execute the queries together as it gives me an error. But when I execute them one after another, it works fine. The queries are as follows:

UPDATE simple_web_content
SET content_data = REPLACE(content_data, '“', '"') WHERE simple_web_content_type = 'B';

UPDATE simple_web_content
SET content_data = REPLACE(content_data, '’', '''') WHERE simple_web_content_type = 'B';

Is there any way I can combine them to a single query to execute them at once.

Upvotes: 1

Views: 741

Answers (2)

Mr. Bill
Mr. Bill

Reputation: 34

What kind of error are you getting when you combine them? Are you combining them like this?

UPDATE simple_web_content 
SET content_data = REPLACE(REPLACE(content_data, '’', ''''), '“', '"') 
WHERE simple_web_content_type = 'B';

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51426

try translate?..

t=# select translate('’fyva’“oldj“','’“',e'\'"');
  translate
--------------
 'fyva'"oldj"

so I suppose for you it would be smth like:

UPDATE simple_web_content
SET content_data = translate(content_data, '“’', e'"\'') 
WHERE simple_web_content_type = 'B';

Upvotes: 1

Related Questions