CODEWITHSUNDEEP
postgresqlreplace
dushyantashu
dushyantashu

Reputation: 543

What is the most readable way to replace multiple strings with replacements in a string in postgresql.

My usecase is something like this :

String = "INDIA CANADA ITALY",
String Post Repacement = "IN CA IT"

I'm looking for something like this : 

replaceMultiple(String, "INDIA", "IN", "CANADA", "CA", "ITALY", "IT")

I'm currently doing this by using nested replace, but it's not readable much, and if I want to add more replacements, I'll have nest it further more.

Can this be achieved by functions, like creating a temporary table in runtime with key-value pairs, and replace every key in string with value. Or some other method?

Upvotes: 2

Views: 830

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246728

There are many ways to do it.

You could use temporary tables and regexp_replace, or you could use a JSON dictionary like this:

SELECT string_agg(
          COALESCE(
             '{ "INDIA": "IN", "CANADA": "CA", "ITALY": "IT" }'::jsonb->>s,
             s
          ),
          ' '
       )
FROM regexp_split_to_table('INDIA CANADA ITALY', '\s') s;

Upvotes: 3

Related Questions