PostgreSQL：提取最后数字后文本及地址字段拆分的实现

针对单行存储的地址拆分需求，以下是基于PostgreSQL正则表达式的精准提取方案，覆盖无空格、无门牌号/室号等各种不规则场景：

1. 室号提取（最后数字后的内容） #

核心逻辑：匹配最后一组数字之后的所有非数字内容，自动忽略数字与室号间的空格或无分隔的情况，无数字时返回空值。

-- 提取室号，自动去除前后空格
TRIM(SUBSTRING(column_name FROM '(?<=\d)(\D+)$')) AS room_number

• 正向后顾(?<=\d)确保只匹配数字之后的内容；(\D+)$匹配结尾的所有非数字字符；TRIM处理可能的空格。

2. 优化门牌号提取（解决原方法准确性不足问题） #

原方法regexp_replace(column_name :: text, '\D', '', 'g')会误提取街道中的数字（如"5th Avenue 123B"会得到"5123"），优化后提取最后一组连续数字作为门牌号：

-- 提取最后一组连续数字作为门牌号，无数字时返回空值
TRIM(SUBSTRING(column_name FROM '(\d+)\D*$')) AS house_number

• (\d+)\D*$匹配结尾的连续数字及后续非数字内容，只捕获数字部分；TRIM处理可能的空格。

3. 街道提取（优化原方法） #

原方法trim(substring(column_name from '[^\d]+'))无法处理街道含数字的场景（如"5th Street 123 B"），优化后移除最后数字及后续内容，保留完整街道：

-- 提取门牌号之前的街道内容，无门牌号时返回原地址
TRIM(REGEXP_REPLACE(column_name, '\d+\D*$', '')) AS street

• \d+\D*$匹配结尾的数字及后续内容，用空字符串替换后得到街道部分；TRIM处理首尾空格。

测试场景验证 #

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