使用正则表达式拆分识别PostgreSQL中格式不规范的手动拼接ID

核心规则 #

• 多个ID以逗号分隔，标准ID格式为前缀.后缀，后缀支持包含字母
• 若ID段以.开头，自动继承最近一个完整ID的前缀
• 自动过滤ID中的空格，字母统一转为大写

实现代码 #

WITH tmp AS (
    SELECT '145.100, 150.200' AS dgunr
    UNION ALL
    SELECT '160.10A'
    UNION ALL
    SELECT ' 10 .  545'
    UNION ALL
    SELECT '132.134, .6577, .456'
    UNION ALL
    SELECT '121.111, .6577, .88c, .999, 112.4444'
),
-- 按逗号拆分ID串为单个片段，保留原始顺序
split_ids AS (
    SELECT 
        dgunr,
        regexp_replace(trim(upper(id_segment)), '\s', '', 'g') AS segment,
        ordinal AS seg_order
    FROM tmp,
         string_to_table(dgunr, ',') WITH ordinality AS t(id_segment, ordinal)
),
-- 拆分每个片段的前缀、后缀部分
split_parts AS (
    SELECT
        dgunr,
        seg_order,
        segment,
        CASE WHEN split_part(segment, '.', 1) <> '' THEN split_part(segment, '.', 1) END AS prefix,
        split_part(segment, '.', 2) AS suffix
    FROM split_ids
),
-- 补全所有缺失的前缀
fill_prefix AS (
    SELECT
        dgunr,
        seg_order,
        suffix,
        last_value(prefix) IGNORE NULLS OVER (PARTITION BY dgunr ORDER BY seg_order) AS final_prefix
    FROM split_parts
)
-- 拼接输出最终ID
SELECT 
    dgunr AS 原ID串,
    final_prefix || '.' || suffix AS 拆分后ID
FROM fill_prefix
ORDER BY dgunr, seg_order;

逻辑说明 #

1. 拆分原字符串：使用string_to_table配合with ordinality按逗号拆分原始ID串，同时保留每个片段的原始顺序，保证前缀继承逻辑符合预期
2. 片段预处理：去除每个片段的前后空格、过滤所有内部空格、统一转为大写，消除格式不规范带来的解析错误
3. 前缀拆分：识别完整ID的前缀，将以.开头的片段的前缀标记为空
4. 缺失前缀补全：通过窗口函数last_value() ignore nulls获取当前片段之前最近的非空前缀，补全所有短ID的前缀
5. 结果拼接：将前缀和后缀拼接为标准格式的完整ID

效果验证 #

该代码可覆盖所有测试场景，针对第6类多前缀切换场景：
输入'121.111, .6577, .88c, .999, 112.4444'，输出为121.111、121.6577、121.88C、121.999、112.4444，完全匹配需求。

内容的提问来源于stack exchange，提问作者one_tick_pony