使用SQL校验指定格式电话号码 正确匹配三类合法手机号

校验目标 #

需要匹配三类合法格式的电话号码，过滤所有非法格式：

• +1(111)111-1111
• (111)111-1111
• 1111111111

原有逻辑缺陷 #

原校验SQL存在两个核心问题，导致结果不符合预期：

1. 字符白名单遗漏+号：第一条规则PhoneNumber NOT LIKE '%[^0-9()-]%'仅允许数字、括号、横杠，带+1前缀的合法号码会直接被过滤，造成漏判。
2. 未校验符号位置：仅通过替换所有符号后判断数字长度为10的逻辑，不限制(、)、-的位置和数量，只要数字总数凑够10个，哪怕括号顺序颠倒、横杠随意插入都会被判定为合法，造成非法号码误判。

修正后的校验逻辑 #

直接针对三类合法格式做固定模式匹配，不需要做字符替换，从格式上严格限制符号位置和数字长度，代码如下：

DECLARE @Customers TABLE (PhoneNumber VARCHAR(20))
INSERT @Customers
VALUES
    ('+1(111)111-1111'),
    ('1111111111'),
    ('11(11)111111'),
    ('11abcd1111'),
    (')123(45678-9-0-'),
    ('(111)111-1111')

SELECT PhoneNumber
FROM @Customers
WHERE 
    -- 匹配+1开头的带括号横杠格式
    PhoneNumber LIKE '+1([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
    -- 匹配括号开头的带横杠格式
    OR PhoneNumber LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
    -- 匹配纯10位数字格式
    OR PhoneNumber LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

执行结果 #

运行上述代码后，会正确返回所有合法号码，过滤全部非法值，返回结果如下：

+1(111)111-1111
1111111111
(111)111-1111

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