SQL将全名字段拆分为名、中间名、姓氏及后缀的实现求助

以下代码基于SQL Server语法实现，可满足你提出的无中间名、无后缀时返回NULL的要求，你可以根据实际使用的数据库语法做少量适配。

核心调整说明 #

• 修正原有中间名提取逻辑，无有效中间名时自动返回NULL
• 调整姓氏提取规则，自动排除后缀内容
• 预设常见英文姓名后缀匹配列表，你可根据业务需求自行增删后缀项

-- 以下为完整计算列代码
,Ltrim(SubString(NAME, 1, Isnull(Nullif(CHARINDEX(' ', NAME), 0), 1000))) AS FirstName
,NULLIF(Ltrim(SUBSTRING(NAME, CharIndex(' ', NAME), CASE
WHEN (CHARINDEX(' ', NAME, CHARINDEX(' ', NAME) + 1) - CHARINDEX(' ', NAME)) <= 0
THEN 0
ELSE CHARINDEX(' ', NAME, CHARINDEX(' ', NAME) + 1) - CHARINDEX(' ', NAME)
END)),'') AS MiddleName
,CASE 
    WHEN LTRIM(RTRIM(SUBSTRING(NAME, LEN(NAME) - CHARINDEX(' ', REVERSE(LTRIM(RTRIM(NAME)))) + 2, LEN(NAME)))) IN ('Jr','Sr','I','II','III','IV','V','PhD','MD','Esq')
    THEN LTRIM(SUBSTRING(NAME, 
        Isnull(Nullif(CHARINDEX(' ', NAME, Charindex(' ', NAME) + 1), 0), CHARINDEX(' ', NAME)),
        LEN(NAME) - CHARINDEX(' ', REVERSE(LTRIM(RTRIM(NAME)))) - Isnull(Nullif(CHARINDEX(' ', NAME, Charindex(' ', NAME) + 1), 0), CHARINDEX(' ', NAME)) + 1
    ))
    ELSE Ltrim(SUBSTRING(NAME, 
        Isnull(Nullif(CHARINDEX(' ', NAME, Charindex(' ', NAME) + 1), 0), CHARINDEX(' ', NAME)), 
        CASE WHEN Charindex(' ', NAME) = 0 THEN 0 ELSE LEN(NAME) END
    ))
END AS LastName
,NULLIF(CASE 
    WHEN LTRIM(RTRIM(SUBSTRING(NAME, LEN(NAME) - CHARINDEX(' ', REVERSE(LTRIM(RTRIM(NAME)))) + 2, LEN(NAME)))) IN ('Jr','Sr','I','II','III','IV','V','PhD','MD','Esq')
    THEN LTRIM(RTRIM(SUBSTRING(NAME, LEN(NAME) - CHARINDEX(' ', REVERSE(LTRIM(RTRIM(NAME)))) + 2, LEN(NAME))))
END,'') AS Suffix

运行效果验证 #

针对你提供的示例数据，运行上述代码输出结果如下：

如果姓名仅包含3段且最后一段不是合法后缀，比如Anna Lee Smith，则Suffix返回NULL，LastName为Smith；如果姓名仅包含2段比如Tom Cruz，则MiddleName返回NULL，LastName为Cruz，Suffix返回NULL。

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