SQL姓名拆分优化：含Dr./Mrs./Mr.称谓的姓名正确提取方案

需求说明 #

源表raw_data.sales的person_name字段包含两种格式：

• 普通姓名：如John Doe（名+姓）
• 带称谓姓名：如Mr. John Doe、Dr. Jane Smith（称谓+名+姓）

需要调整查询，让first_name列包含「普通姓名的名」或「称谓+名」，last_name列仅保留姓氏。

MySQL实现代码 #

SELECT
  person_name,
  CASE
    -- 判断是否以指定称谓开头
    WHEN person_name REGEXP '^(Dr\\.|Mrs\\.|Mr\\.)' THEN 
      -- 提取从开头到倒数第一个空格前的所有内容（含称谓和名）
      TRIM(SUBSTRING_INDEX(person_name, ' ', LENGTH(person_name) - LENGTH(REPLACE(person_name, ' ', ''))))
    -- 普通姓名直接提取第一个空格前的部分
    ELSE TRIM(SUBSTRING_INDEX(person_name, ' ', 1))
  END AS first_name,
  -- 统一提取最后一个空格后的部分作为姓氏
  TRIM(SUBSTRING_INDEX(person_name, ' ', -1)) AS last_name
FROM raw_data.sales;

PostgreSQL实现代码 #

SELECT
  person_name,
  CASE
    WHEN person_name ~ '^(Dr\.|Mrs\.|Mr\.)' THEN 
      -- 移除最后一个空格及后面的内容，保留称谓+名
      TRIM(REGEXP_REPLACE(person_name, '\\s+[^\\s]+$', ''))
    ELSE TRIM(SPLIT_PART(person_name, ' ', 1))
  END AS first_name,
  TRIM(SPLIT_PART(person_name, ' ', -1)) AS last_name
FROM raw_data.sales;

补充说明 #

• 若存在多个连续空格的情况，TRIM函数会自动清理首尾多余空格
• 需要支持更多称谓（如Ms.）时，直接在正则表达式里追加即可，比如^(Dr\\.|Mrs\\.|Mr\\.|Ms\\.)
• 若有姓名仅含单个名称（无姓氏），可添加额外CASE分支处理：比如当LENGTH(person_name) - LENGTH(REPLACE(person_name, ' ', '')) = 0时，将last_name设为NULL或空字符串

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