如何在GCP BigQuery中提取仅含美国本土号码的纯数字手机号

需求：从phone_number列中，仅提取国家码为+1的号码对应的区号及后续号码的纯数字部分，其他国家码的记录返回NULL。

原始数据示例 #

phone_number
+1 414-935-5896
+1 (408) 832-3423
+91 90353 45657

期望结果 #

US_phone_number_only
4149355896
4088323423
NULL

解决方案SQL #

SELECT
  phone_number,
  CASE
    -- 判断号码是否以+1开头
    WHEN REGEXP_CONTAINS(phone_number, r'^\+1') THEN
      -- 先提取+1之后的所有内容，再移除所有非数字字符
      REGEXP_REPLACE(REGEXP_EXTRACT(phone_number, r'^\+1\s*(.*)'), r'\D', '')
    ELSE NULL
  END AS US_phone_number_only
FROM `your-project.your-dataset.your-table` -- 替换为你的实际表路径

逻辑说明 #

• REGEXP_CONTAINS(phone_number, r'^\+1')：校验号码是否以+1开头，筛选美国号码
• REGEXP_EXTRACT(phone_number, r'^\+1\s*(.*)')：提取+1及后续空格之后的所有内容（比如414-935-5896或(408) 832-3423）
• REGEXP_REPLACE(..., r'\D', '')：把提取到的内容里的所有非数字字符（包括括号、横杠、空格）替换为空，得到纯数字串
• CASE语句：满足条件返回处理后的纯数字，否则返回NULL

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