PostgreSQL中如何为无前缀55的手机号字符串开头添加55

适配PostgreSQL的修改后查询方案 #

SELECT leads.name as "lead's name",
       CASE
           WHEN cleaned_phone IS NOT NULL AND NOT starts_with(cleaned_phone, '55')
           THEN '55' || cleaned_phone
           ELSE cleaned_phone
       END as "lead's phone"
FROM (
    SELECT 
        name,
        created_at,
        NULLIF(regexp_replace(phone, '\D','','g'), '') AS cleaned_phone
    FROM leads
) AS cleaned_leads
ORDER BY created_at DESC

逻辑说明 #

• 子查询先完成手机号非数字字符清洗、空值处理，避免上层逻辑重复编写正则表达式，维护更方便
• 用PostgreSQL原生的starts_with函数直接判断手机号前缀，逻辑清晰易读
• 仅在清洗后的手机号非空、且开头不是55时才添加前缀，避免给空值强行加前缀的异常情况
• 如果需要兼容旧版本PostgreSQL，可将starts_with(cleaned_phone, '55')替换为LEFT(cleaned_phone, 2) = '55'，执行效果完全一致
• 若需要返回数值格式，只需将CASE语句整体加::bigint强制转换即可，符合格式要求

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