替代在 PostgreSQL 函数中嵌套正则表达式替换

如果您将一串数据拆分为其他区域，那么替换区域对您来说可能很容易.例如:

with tb as (
    select 1 as id, 'NORTH AMERICA - USA - USA - NORTHEAST - Massachusetts - Boston Metro' as record_region_input
    union all 
    select 2 as id, 'NORTH AMERICA - USA - USA - MIDATLANTIC - Virginia'
    union all 
    select 3 as id, 'Rest of the World - ASIA - China - Shanghai Metro' 
)
select * from (
    select distinct tb.id, unnest(string_to_array(record_region_input, ' - ')) as region from tb 
    order by tb.id 
) a1 where a1.region not in ('NORTH AMERICA', 'Rest of the World');

-- Result: 
1   Boston Metro
1   Massachusetts
1   NORTHEAST
1   USA
2   MIDATLANTIC
2   USA
2   Virginia
3   ASIA
3   China
3   Shanghai Metro

然后，例如，对于复制区域，您可以使用distinct，对于不必要的区域，您可以使用NOT in，并且您可以使用像"%ASIA%"这样的命令来获取包含ASIA等的所有区域.在所有处理之后，您可以再次合并更正的字符串.例子:

with tb as (
    select 1 as id, 'NORTH AMERICA - USA - USA - NORTHEAST - Massachusetts - Boston Metro' as record_region_input
    union all 
    select 2 as id, 'NORTH AMERICA - USA - USA - MIDATLANTIC - Virginia'
    union all 
    select 3 as id, 'Rest of the World - ASIA - China - Shanghai Metro' 
)
select a1.id, string_agg(a1.region, ' - ')  from (
    select distinct tb.id, unnest(string_to_array(record_region_input, ' - ')) as region from tb 
    order by tb.id 
) a1 where a1.region not in ('NORTH AMERICA', 'Rest of the World')
group by a1.id 

-- Return: 
1   Boston Metro - Massachusetts - NORTHEAST - USA
2   MIDATLANTIC - USA - Virginia
3   ASIA - China - Shanghai Metro

这是一个简单的 idea ，也许这个 idea 可以帮助你替换区域.