SQL Server拆分FullName为姓名列时遇Msg 537错误的解决

错误原因 #

当FullName列中存在无空格的记录（如'Seyhan'）时，CHARINDEX(' ', FullName)会返回0，此时原查询中SUBSTRING(FullName, 1, Charindex(' ', FullName) - 1)的第三个参数会变成-1——SQL Server不允许SUBSTRING的长度参数为负数，因此触发Msg 537错误。

同时原查询的判断条件(LEN(FullName) - LEN(REPLACE(FullName, ' ', '')) + 1) > 0存在逻辑问题：对于无空格的记录，LEN(FullName) - LEN(REPLACE(...))结果为0，加1后变为1>0，会错误地让无空格记录进入THEN分支执行报错的SUBSTRING。

修正后的查询 #

以下查询会正确处理两种场景：含空格的全名拆分出FirstName和LastName，无空格的记录将FullName赋值给FirstName，LastName留空：

SELECT  
    CASE
        WHEN CHARINDEX(' ', FullName) > 0 
            THEN SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1)
        ELSE FullName
    END AS FirstName,      
    CASE
        WHEN CHARINDEX(' ', FullName) > 0 
            THEN REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1))
        ELSE ''
    END AS LastName,
    email, 
    c.customerid
FROM Customer c

逻辑说明 #

1. FirstName处理：通过CHARINDEX(' ', FullName) > 0判断是否存在空格，存在则截取第一个空格前的内容；不存在则直接返回FullName。
2. LastName处理：同样判断空格存在性，存在则通过反转字符串找到最后一个空格的位置，截取反转后第一个空格前的内容再反转回来（即原字符串最后一个空格后的内容）；不存在则返回空字符串。

简化写法（可选） #

如果你的SQL Server版本是2016及以上，可以使用STRING_SPLIT结合排序实现更简洁的拆分，不过兼容性稍差：

SELECT
    (SELECT TOP 1 value FROM STRING_SPLIT(FullName, ' ') ORDER BY CHARINDEX(value, FullName)) AS FirstName,
    (SELECT TOP 1 value FROM STRING_SPLIT(FullName, ' ') ORDER BY CHARINDEX(value, FullName) DESC) AS LastName,
    email,
    customerid
FROM Customer c

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