Multiple string substitutions in PostgreSQL

Question

I have a column with abbreviations separated by spaces like this

'BG MSG'

Also, there's another table with substitutions

target  replacement
----------------------
'BG',  'Brick Galvan'
'MSG', 'Mosaic Galvan'

The goal is to apply all the substitutions to the abbreviations to obtain something like

'Brick Galvan Mosaic Galvan' from 'BG MSG'

I know I could do

replace( replace('BG MSG', 'BG', 'Brick Galvan'), 'MSG', 'Mosaic Galvan')

But imagine there are hundreds of substitutions, and they can change from one day to the next. The resulting query will be hideous to maintain.

I mean, I could do a code generator that will create the query with all the nested replaces, but I'm looking for something more elegant and postgres-native.

I've found solutions like this one How to replace multiple special characters in Postgres 9.5 but they seem to work only for single characters.

I guess you can use a custom aggregate, or some loop in a PL/PGSQL function. — Bergi, Apr 21 '20 at 20:33

score 3 · Accepted Answer · answered Apr 21 '20 at 23:12

3

Let's say your tables look like this:

create table my_table(id serial primary key, abbrevs text);
insert into my_table (abbrevs) values
('BG MSG');

create table substitutions(target text, replacement text);
insert into substitutions values
('BG',  'Brick Galvan'),
('MSG', 'Mosaic Galvan');

You can get each abbreviation in a single row:

select id, unnest(string_to_array(abbrevs, ' ')) as abbrev
from my_table

 id | abbrev
----+--------
  1 | BG
  1 | MSG
(2 rows)

and use them to join the substitution table and get full names:

select id, string_agg(replacement, ' ') as full_names
from (
    select id, unnest(string_to_array(abbrevs, ' ')) as abbrev
    from my_table
    ) t
join substitutions on abbrev = target
group by id

 id |         full_names
----+----------------------------
  1 | Brick Galvan Mosaic Galvan
(1 row)

Db<>fiddle.

answered Apr 21 '20 at 23:12

klin

112,967
15
204
232

at first sight it seems your solution it's what I'm looking for. Thanks. Will check and probably accept the answer – Way Too Simple Apr 22 '20 at 16:16
reviewed your solution and works great. It's really a postgres way of doing things. Every time you need to do something complex, you should think of building intermediate tables where your task is simpler. At least is what have worked for me. Thanks again klin. – Way Too Simple Apr 22 '20 at 19:28
I'm glad I could help, saludos! – klin Apr 22 '20 at 20:22

yanes · Answer 2 · 2020-04-22T12:04:08.587

Nested replace approach would work but it is quite ugly, right?

SELECT REPLACE(REPLACE(REPLACE(REPLACE(…

After carefully formatted to make it look readable, the best you can get follows:

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(...

On the other hand, you might just use the LATERAL JOIN solution which uses more characters but, it is definitely more readable.

-- Input: BG, MSG
-- Output: Brick Galvan, Mosaic Galvan
SELECT msg.Materials
FROM (SELECT 'BG, MSG' AS Materials) mt
         INNER JOIN LATERAL (SELECT REPLACE(mt.Materials::text, 'BG', 'Brick Galvan') AS Materials) bg ON true
         INNER JOIN LATERAL (SELECT REPLACE(bg.Materials::text, 'MSG', 'Mosaic Galvan') AS Materials) msg ON true;

Multiple string substitutions in PostgreSQL

2 Answers2