How to replace an array of characters in a column of text in PostgreSQL?

Question

I have 2 text columns where I need to replace (on update) chars from array 1 ('q','x','y','z') to their index-equivalent value in array 2 ('a','b','c','d').

The closest I've come to atm is to nest the replace calls within each other like so

UPDATE 
    mytable 
SET 
    col1=replace(
            replace(
                replace(
                    replace(
                        col1,'q','a'
                    ),'x','b'
                ),'y','c'
            ),'z','d'
        ),
    col2=replace(
            replace(
                replace(
                    replace(
                        col2,'q','a'
                    ),'x','b'
                ),'y','c'
            ),'z','d'
        )

but surely there must be a better way to do this? In my live case I have 14 of those char pairs. If it has any relevance - the chars are a mix of japanese hieroglyphs and accented letters from Swedish alphabet.

This question is not good because reader is looking for a real string-replace problem, not character-replace... So, changing title from "How to replace an array of strings..." to "How to replace an array of characters...". — Peter Krauss
– Peter Krauss, Commented Aug 2, 2020 at 14:54

roman · Accepted Answer · 2013-11-13 15:25:28Z

2

PostgreSQL have special function for this, translate():

update mytable set
    col1 = translate(col1, 'qxyz', 'abcd'),
    col2 = translate(col2, 'qxyz', 'abcd')

sql fiddle example

answered Nov 13, 2013 at 15:25

roman

118k30 gold badges205 silver badges209 bronze badges

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

Collectives™ on Stack Overflow

How to replace an array of characters in a column of text in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related