SQL姓名处理：正确拆分姓与名并重组为「名 姓」格式

已在Stack上搜索相关方案，目前接近需求，但遇到问题：当员工姓名仅包含姓和名时，名会被错误识别为中间名。需要提取姓和名，重组为「名 姓」格式展示。

示例数据 #

期望输出 #

当前使用的SQL代码 #

SELECT DISTINCT
 EMP_ID
,EMP_NAME
,SUBSTRING(EMP_NAME, 1, CHARINDEX(',', EMP_NAME) - 1) AS LASTNAME
,CASE WHEN PARSENAME(REPLACE(EMP_NAME, ',', '.'),1) LIKE '% %' THEN PARSENAME(REPLACE(PARSENAME(REPLACE(EMP_NAME, ',', '.'),1), ' ', '.'),2) ELSE PARSENAME(REPLACE(EMP_NAME, ',', '.'),1) END FIRSTNAME
,CASE WHEN PARSENAME(REPLACE(EMP_NAME, ' ', '.'),1) LIKE '%,%' THEN NULL ELSE PARSENAME(REPLACE(EMP_NAME, ' ', '.'),1) END MI

FROM EMP_TABLE

修正后的SQL方案 #

针对名被错误识别为中间名的问题，调整逻辑直接提取逗号后第一个空格前的内容作为名，不管是否存在中间名，同时直接重组目标格式：

SELECT DISTINCT
    EMP_ID,
    EMP_NAME,
    SUBSTRING(EMP_NAME, 1, CHARINDEX(',', EMP_NAME) - 1) AS LASTNAME,
    -- 提取逗号后第一个空格前的内容作为名，自动处理无中间名的情况
    LTRIM(SUBSTRING(EMP_NAME, CHARINDEX(',', EMP_NAME) + 1, CHARINDEX(' ', EMP_NAME + ' ', CHARINDEX(',', EMP_NAME) + 1) - CHARINDEX(',', EMP_NAME) - 1)) AS FIRSTNAME,
    -- 可选提取中间名，无中间名时返回NULL
    CASE 
        WHEN CHARINDEX(' ', EMP_NAME, CHARINDEX(',', EMP_NAME) + 1) < LEN(EMP_NAME) 
        THEN LTRIM(SUBSTRING(EMP_NAME, CHARINDEX(' ', EMP_NAME, CHARINDEX(',', EMP_NAME) + 1) + 1, LEN(EMP_NAME))) 
        ELSE NULL 
    END AS MI,
    -- 重组为「名 姓」格式的完整姓名
    CONCAT(
        LTRIM(SUBSTRING(EMP_NAME, CHARINDEX(',', EMP_NAME) + 1, CHARINDEX(' ', EMP_NAME + ' ', CHARINDEX(',', EMP_NAME) + 1) - CHARINDEX(',', EMP_NAME) - 1)),
        ' ',
        SUBSTRING(EMP_NAME, 1, CHARINDEX(',', EMP_NAME) - 1)
    ) AS EMP_NAME_FULL
FROM EMP_TABLE

逻辑说明 #

1. LASTNAME：保持原有逻辑，提取逗号前的内容作为姓氏
2. FIRSTNAME：通过定位逗号位置，提取逗号后到第一个空格前的内容，用LTRIM去除前置空格，同时在EMP_NAME后拼接空格，确保无中间名时也能正确截取到全名
3. MI：判断逗号后是否存在后续空格，存在则提取空格后的内容作为中间名，否则返回NULL
4. EMP_NAME_FULL：直接拼接提取到的名和姓，生成「名 姓」格式的目标字段

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