Python与PostgreSQL正则语法差异及地址解析转换方案咨询

一、核心语法差异 #

Python和PostgreSQL的正则引擎存在关键差异：

• 大小写修饰符规则：Python的(?i)可放在正则任意位置，仅作用于后续内容；PostgreSQL要求全局大小写不敏感修饰符要么放在正则最开头（如(?i)），要么通过函数的flag参数指定（如'i'），否则会触发语法错误。
• 匹配行为细节：Python的re.match仅匹配字符串开头，re.search匹配任意位置；PostgreSQL的regexp_matches默认返回所有匹配结果，需结合^锚定开头来模拟re.match的行为。

二、Python函数转PostgreSQL语法 #

1. 解析街道名称（对应Python的parse_street） #

Python逻辑：提取字符串开头到不区分大小写的no词边界前的内容，去除首尾空格。
PostgreSQL实现：

-- 写法1：将修饰符放在正则开头
SELECT trim((regexp_matches('Jl. ABC No.01', '^(?i)(.*?)\b no\b'))[1]) AS street;
-- 写法2：通过flag参数指定不区分大小写，可读性更强
SELECT trim((regexp_matches('Jl. ABC No.01', '^(.*?)\b no\b', 'i'))[1]) AS street;

2. 解析门牌号（对应Python的parse_housenumber） #

Python逻辑：匹配不区分大小写的no（可带点号）后的数字/字母组合。
PostgreSQL实现：

-- 写法1：内联修饰符放在开头
SELECT (regexp_matches('Jl. ABC No.01', '(?i)\bno\.?\s*([\dA-Z]+)'))[1] AS house_number;
-- 写法2：使用flag参数
SELECT (regexp_matches('Jl. ABC No.01', '\bno\.?\s*([\dA-Z]+)', 'i'))[1] AS house_number;

三、批量处理表中数据 #

假设你的表名为address_table，address是存储完整地址的字段，以下SQL可直接生成street和house_number字段：

SELECT 
    address,
    -- 处理街道名称，无匹配时返回NULL
    CASE 
        WHEN regexp_matches(address, '^(.*?)\b no\b', 'i') IS NOT NULL 
        THEN trim((regexp_matches(address, '^(.*?)\b no\b', 'i'))[1])
        ELSE NULL 
    END AS street,
    -- 处理门牌号，无匹配时返回NULL
    CASE 
        WHEN regexp_matches(address, '\bno\.?\s*([\dA-Z]+)', 'i') IS NOT NULL 
        THEN (regexp_matches(address, '\bno\.?\s*([\dA-Z]+)', 'i'))[1]
        ELSE NULL 
    END AS house_number
FROM address_table;

如果需要将结果更新到表中（需先添加street和house_number字段）：

UPDATE address_table
SET 
    street = CASE 
                WHEN regexp_matches(address, '^(.*?)\b no\b', 'i') IS NOT NULL 
                THEN trim((regexp_matches(address, '^(.*?)\b no\b', 'i'))[1])
                ELSE NULL 
            END,
    house_number = CASE 
                    WHEN regexp_matches(address, '\bno\.?\s*([\dA-Z]+)', 'i') IS NOT NULL 
                    THEN (regexp_matches(address, '\bno\.?\s*([\dA-Z]+)', 'i'))[1]
                    ELSE NULL 
                END;

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