PostgreSQL电话号码格式校验与多列拆分技术问询

测试数据 #

with t (id,telephone) as (values
(1234560,'(345) 908-1234 '),
(1234561,'(345) 908-1234;ext=1234'),
(1234562 ,'(345) 908-1234;ext=12345'),
(1234563 ,'(345) 908-12345;ext=12345'),
(1234564 ,'1234567;ext=12345'),
(1234565 ,'123ABC;ext=12345'),
(1234566 ,'Ext=12345'),
(1234567 ,'+1123456789')
)
select * from t

格式规则定义 #

仅以下两种格式视为有效电话号码：

• (XXX) XXX-XXXX
• (XXX) XXX-XXXX;ext=XXX（ext部分长度不限）

其余格式均为无效，需按规则处理。

处理逻辑 #

Case 1：有效格式处理（已实现） #

有效号码需拆分至以下字段：

• Area：括号内前3位数字
• Exchange：空格后、横杠前的3位数字
• Line：横杠后的4位数字
• Ext number：;ext=之后的所有内容

Case 2：无效格式处理（解决场景1问题） #

场景1：需忽略的无效格式

若号码满足以下任一条件，所有输出字段设为NULL：

• 包含A-Z字母（大小写不限）
• 仅为Ext=XXXX格式（大小写不限）

场景2：其他无效格式

• Alternate number：;ext=之前的内容（无;ext=则取原号码）
• Ext：;ext=之后的内容（无则为NULL）

最终SQL实现 #

with t (id,telephone) as (values
(1234560,'(345) 908-1234 '),
(1234561,'(345) 908-1234;ext=1234'),
(1234562 ,'(345) 908-1234;ext=12345'),
(1234563 ,'(345) 908-12345;ext=12345'),
(1234564 ,'1234567;ext=12345'),
(1234565 ,'123ABC;ext=12345'),
(1234566 ,'Ext=12345'),
(1234567 ,'+1123456789')
)
select
    id,
    telephone,
    -- 有效格式提取字段
    case when telephone ~* '^\(\d{3}\) \d{3}-\d{4}(;ext=\d+)?\s*$' then substring(telephone from '\((\d{3})\)') end as area,
    case when telephone ~* '^\(\d{3}\) \d{3}-\d{4}(;ext=\d+)?\s*$' then substring(telephone from '\) (\d{3})-') end as exchange,
    case when telephone ~* '^\(\d{3}\) \d{3}-\d{4}(;ext=\d+)?\s*$' then substring(telephone from '-(\d{4})') end as line,
    case when telephone ~* '^\(\d{3}\) \d{3}-\d{4}(;ext=\d+)?\s*$' then substring(telephone from ';ext=(\d+)') end as ext_number,
    -- 无效格式处理Alternate number
    case
        when telephone ~* '[a-z]' or telephone ~* '^ext=\d+$' then null
        when telephone ~* ';ext=' then substring(telephone from '^(.*?);ext=')
        else telephone
    end as alternate_number,
    -- 无效格式处理Ext
    case
        when telephone ~* '[a-z]' or telephone ~* '^ext=\d+$' then null
        when telephone ~* ';ext=' then substring(telephone from ';ext=(.*)$')
        else null
    end as ext
from t;

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