SQL中如何拆分COALESCE函数返回结果为电话号码、区号两列

现有三张数据表：

• Person_Table
• Phone_Table
• Primary_Phone_Table

主手机号表和非主手机号表中存储的所有电话号码格式均为「区号-电话号码」（如123-5434）或Null。需要在SELECT查询中，从Phone_Number字段提取区号，作为单独的Area_Code列输出。

目前使用的查询语句如下：

SELECT person_number,
       COALESCE ((SELECT phone_number
                    FROM Primary_phone_table PPHT
                   WHERE PPHT.person_id = PT.person_id),
                 (SELECT phone_number
                    FROM phone_table PHT
                   WHERE PHT.person_id = PT.person_id)) Phone_Number
  FROM Person_Table PT

期望输出的字段包含：Person_Number、Phone_Number、Area_Code三列。

你可以将原有查询作为子查询嵌套，对合并得到的Phone_Number做字符串截取，取第一个-之前的内容作为Area_Code即可，以下是适配绝大多数数据库的通用实现方案：

SELECT 
  person_number,
  Phone_Number,
  CASE 
    WHEN Phone_Number LIKE '%-%' THEN SUBSTRING(Phone_Number, 1, CHARINDEX('-', Phone_Number) - 1)
    ELSE NULL 
  END AS Area_Code
FROM (
  -- 原有获取合并手机号的逻辑
  SELECT 
    person_number,
    COALESCE(
      (SELECT phone_number FROM Primary_phone_table PPHT WHERE PPHT.person_id = PT.person_id),
      (SELECT phone_number FROM phone_table PHT WHERE PHT.person_id = PT.person_id)
    ) AS Phone_Number
  FROM Person_Table PT
) AS T

如果使用特定数据库，可以用更简化的写法：

• MySQL/PostgreSQL 可使用SUBSTRING_INDEX函数：

SELECT 
  person_number,
  Phone_Number,
  IF(Phone_Number LIKE '%-%', SUBSTRING_INDEX(Phone_Number, '-', 1), NULL) AS Area_Code
FROM (
  SELECT 
    person_number,
    COALESCE(
      (SELECT phone_number FROM Primary_phone_table PPHT WHERE PPHT.person_id = PT.person_id),
      (SELECT phone_number FROM phone_table PHT WHERE PHT.person_id = PT.person_id)
    ) AS Phone_Number
  FROM Person_Table PT
) AS T

• Oracle 可使用INSTR替代CHARINDEX：

SELECT 
  person_number,
  Phone_Number,
  CASE 
    WHEN INSTR(Phone_Number, '-') > 0 THEN SUBSTR(Phone_Number, 1, INSTR(Phone_Number, '-') - 1)
    ELSE NULL 
  END AS Area_Code
FROM (
  SELECT 
    person_number,
    COALESCE(
      (SELECT phone_number FROM Primary_phone_table PPHT WHERE PPHT.person_id = PT.person_id),
      (SELECT phone_number FROM phone_table PHT WHERE PHT.person_id = PT.person_id)
    ) AS Phone_Number
  FROM Person_Table PT
) AS T

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