string - PostgreSQL 中的多个字符串替换

Question

我有一列缩写，像这样用空格分隔

'BG MSG'

此外，还有另一个带有替换的表

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

目标是将所有替换应用于缩写以获得类似的内容

“Brick Galvan Mosaic Galvan” 来自 “BG MSG”

我知道我能做到

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

但是想象一下有数百个替代品，并且它们可能一天又一天地发生变化。生成的查询将难以维护。

我的意思是，我可以做一个代码生成器来创建包含所有嵌套替换的查询，但我正在寻找更优雅和 postgres 原生的东西。

我找到了像这样的解决方案 How to replace multiple special characters in Postgres 9.5但它们似乎只适用于单个字符。

Answer 1

假设您的表格如下所示:

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');

您可以在一行中获取每个缩写:

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

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

并使用它们加入替换表并获取全名:

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.