CODEWITHSUNDEEP
stringpostgresqlreplace
Way Too Simple
Way Too Simple

Reputation: 305

Multiple string substitutions in PostgreSQL

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.

Upvotes: 2

Views: 887

Answers (2)

yanes
yanes

Reputation: 63

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;

Upvotes: 2

klin
klin

Reputation: 121634

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.

Upvotes: 3

Related Questions