PostgreSQL 函数中嵌套正则表达式替换的替代方案？

Question

目前，我有一个杂乱无章的替换，有条件的弦乐替换和替换为开放文本字段的视图 - 在此示例中，区域分类。

（请忽略地理的准确性，我只是在处理历史标准作业。此外，我知道我可以替换速度，甚至可以清洁正则票据语句以进行回顾 - 我只是在问有关变量的问题/嵌套在这里。）

    CREATE OR REPLACE FUNCTION public.region_cleanup(record_region text)
     RETURNS text
     LANGUAGE sql
     STRICT
    AS $function$
    SELECT  REGEXP_REPLACE(
            REGEXP_REPLACE(
            REGEXP_REPLACE(
            REGEXP_REPLACE(
            REGEXP_REPLACE(
            REGEXP_REPLACE(record_region,'(NORTH AMERICA\s\-\sUSA\s\-\sUSA)','USA')
            ,'Rest\sof\sthe\sWorld\s\-\s','')
            ,'NORTH\sAMERICA\s\-\sCANADA','NORTH AMERICA - Canada')
            ,'\&amp\;','&')
            ,'Georgia\s\-\sGeorgia','MIDDLE EAST - Georgia')
            ,'EUROPE - Turkey','MIDDLE EAST - Turkey')

使用此功能的示例输出在我的数据集中看起来像这样，拉出了受影响的记录（有些已经以正确的格式）：

record_region_input	record_red_region_egion_output
north America-美国 - 美国 - 美国 - 东北 - 马萨诸塞州 - 马萨诸塞州 - 美国波士顿大都会	- 东北 - 马萨诸塞州 - 波士顿地铁
北美 - 美国 - 美国 - 美国 - 中大西洋 -	美国 - 美国 - 中大西洋 -
世界其他地区 - 亚洲 - 亚洲 - 泰国	亚洲 - 泰国 - 泰国
其他地区 - 欧洲 - 欧洲 - 葡萄牙 - 欧洲 -	欧洲 - 葡萄牙
剩下的葡萄牙世界 - 亚洲 - 中国 - 上海大都会	- 中国 - 佐治亚州
上海 - 佐治亚州	中东 - 佐治亚州

这是...很好。需要正则是正则是因为在这些字符串之前或之后可能发生的情况有很多可变性，并且我在其他地方有适当的验证列表。这只是常见的历史命名问题的大量灌木丛。

问题在于，对于公司命名或跨部门标准之类的东西，我获得了数百种此类“已知替代”（100+）。拥有数十个regexp_replace（嵌套语句使编辑/添加/丢弃任何东西都是令人讨厌的计数游戏。

我正在尝试专门清洁Postgres中的数据，因为我当前的管道并不总是允许 标准化。

上传之前的 /使用示例函数的输出表。

Answer 1

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

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

之后，例如，对于重复区域，您可以使用不同的区域，对于不必要的区域，您可以使用 NOT in，并且您可以使用 like '%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

这是一个简单的想法，也许这个想法可以帮助你替换区域。