如何通过SQL从ODLN表Address字段提取6位邮政编码
从ODLN表Address字段提取末尾6位邮政编码的SQL实现
Address字段存储的长文本中,目标邮政编码存在两个固定特征:为6位纯数字、嵌入在整条文本的末尾位置。基于这两个特征做定向匹配,既不需要逐段拆分复杂的地址文本,也不会被地址中间夹杂的楼号、门牌号数字干扰。
基础匹配规则
设置两层校验逻辑从根源上避免提取错误:
- 严格匹配纯6位数字串,排除文本中其他位置的零散数字
- 仅抓取文本末尾位置的匹配结果,和业务场景下邮编的存储位置对齐
各主流数据库可直接运行的查询语句
- MySQL 8.0及以上版本
通过内置正则函数定位提取,语句为:SELECT DISTINCT REGEXP_SUBSTR(Address, '[0-9]{6}$') AS postcode FROM ODLN -- 过滤无有效末尾邮编的行,避免输出空值 WHERE REGEXP_LIKE(Address, '[0-9]{6}$'); - PostgreSQL 数据库
通过内置正则截取函数实现,语句为:SELECT DISTINCT SUBSTRING(Address FROM '[0-9]{6}$') AS postcode FROM ODLN -- 过滤无有效邮编的行 WHERE Address ~ '[0-9]{6}$'; - Oracle 数据库
正则语法兼容MySQL的正则实现,语句为:SELECT DISTINCT REGEXP_SUBSTR(Address, '[0-9]{6}$') AS postcode FROM ODLN WHERE REGEXP_LIKE(Address, '[0-9]{6}$'); - SQL Server 2017及以上版本
针对不支持复杂正则的版本,通过右截取+数字校验实现,语句为:SELECT DISTINCT RIGHT(Address, 6) AS postcode FROM ODLN -- 校验截取内容为纯6位数字,避免把非数字后缀误判为邮编 WHERE ISNUMERIC(RIGHT(Address, 6)) = 1 AND LEN(RIGHT(Address, 6)) = 6;
兼容特殊场景的优化写法
如果部分地址在邮政编码后面还附带尾部空格、配送备注标点、换行符等非数字冗余内容,可以先对地址文本做清洗,去掉所有末尾非数字字符后再提取,避免漏抓。以MySQL为例的优化版本:
SELECT DISTINCT -- 去除地址末尾所有非数字内容、清理首尾空格后,提取末尾6位数字 REGEXP_SUBSTR( TRIM(REGEXP_REPLACE(Address, '[^0-9]+$', '')), '[0-9]{6}$' ) AS postcode FROM ODLN WHERE REGEXP_LIKE( TRIM(REGEXP_REPLACE(Address, '[^0-9]+$', '')), '[0-9]{6}$' );
内容的提问来源于stack exchange,提问作者Vigna Hari Karthik
相关产品推荐
相关产品推荐

