Postgres - UPDATE a column multiple times with the same query

Question

I have a post table which has a title and a description.

My goal is to clean the description by applying a set of rules. For simplicity let's say that I'd like to apply following rules:

REPLACE "H" with "Z"
REPLACE "Z" with "C"
REPLACE "C" with "X"

So if I have the string "Hello world", the output would be "Xello world" after applying the three rules.

Hello world -> Zello world -> Cello world -> Xello world

I know I can achieve this using 3 independent update queries

UPDATE post
SET description = REPLACE(description, 'H', 'Z');

UPDATE post
SET description = REPLACE(description, 'Z', 'C');

UPDATE post
SET description = REPLACE(description, 'C', 'X');

But I am wondering if there is a better way to achieve what I want. Maybe I need a procedure? a function? store the middle result in a variable?

I am using Postgres 12.

forpas · Accepted Answer · 2021-02-10 22:03:33Z

4

As I understand your requirement, what you want is all the characters 'H', 'Z' and 'C' to be finally replaced by X.
You can do it with the function TRANSLATE():

UPDATE post
SET description = translate(description, 'HZC', 'XXX');

answered Feb 10, 2021 at 22:03

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2021-02-10 21:54:46Z

3

You can nest the replace calls:

UPDATE post
  SET description = replace(replace(replace(description, 'H', 'Z'), 'Z', 'C'), 'C', 'X')

answered Feb 10, 2021 at 21:54

user330315

Comments

Garett · Accepted Answer · 2021-02-10 21:55:36Z

1

How about

UPDATE post
SET description = REPLACE(REPLACE(REPLACE(description, 'H', 'Z'), 'Z', 'C'), 'C', 'X');

answered Feb 10, 2021 at 21:55

Garett

16.9k5 gold badges58 silver badges63 bronze badges

Collectives™ on Stack Overflow

Postgres - UPDATE a column multiple times with the same query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related