SQL中RIGHT、LEFT和CHARINDEX执行不符合预期，拆分姓名列结果异常

核心代码逻辑错误 #

你现有SQL中提取名字的RIGHT函数参数计算逻辑本身有问题：RIGHT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)是从字符串右侧截取分隔符位置减1个字符作为名字，这个逻辑只有当名字长度恰好等于分隔符位置减1时才会返回正确结果，一旦名字长度和这个值不符，就会出现截断或者多截的问题，这是导致第8行名字被截断的主要原因。RIGHT函数是从字符串右侧开始截取N个字符，你想要拿到逗号后面的全部名字内容，正确的截取长度应该是总字段长度 - 分隔符的位置，也就是LEN(NAME_BAT) - CHARINDEX(', ', NAME_BAT)。

数据格式不统一问题 #

你的代码硬编码匹配, （逗号+单个空格）作为姓和名的唯一分隔符，如果实际NAME_BAT字段存在不符合该分隔规则的异常值，就会出现额外问题：

• 第5行出现前置逗号和空格，是因为该行字段内除了姓名分隔用的逗号外，额外存在, 组合（比如录入错误多打了逗号、姓名带后缀前加逗号），导致CHARINDEX匹配到了错误的分隔位置，提取内容时把多余的逗号也包含了进去。
• 部分行如果存在逗号前带空格、逗号后无空格/多空格、甚至无逗号的情况，也会导致CHARINDEX返回值不符合预期，出现各种截取错误。

首先修正RIGHT函数的截取长度逻辑，再增加异常分支兼容没有匹配到分隔符的情况：

SELECT TOP (100) NAME_BAT
    , CASE 
        WHEN CHARINDEX(', ', NAME_BAT) > 0 THEN LTRIM(RIGHT(NAME_BAT, LEN(NAME_BAT) - CHARINDEX(', ', NAME_BAT)))
        ELSE NAME_BAT 
      END AS FIRST_NAME
    , CASE 
        WHEN CHARINDEX(', ', NAME_BAT) > 0 THEN RTRIM(LEFT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1))
        ELSE '' 
      END AS LAST_NAME
    , CASE 
        WHEN CHARINDEX(', ', NAME_BAT) > 0 THEN LTRIM(RIGHT(NAME_BAT, LEN(NAME_BAT) - CHARINDEX(', ', NAME_BAT))) + ' ' + RTRIM(LEFT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1))
        ELSE NAME_BAT 
      END AS NAME_FULL
FROM pitch_aggregate
;

如果数据中还存在逗号后无空格的情况，可以先把所有逗号替换为, 再处理，进一步兼容异常格式：

WITH processed_name AS (
    SELECT TOP (100) 
        NAME_BAT,
        REPLACE(REPLACE(NAME_BAT, ',', ', '), ',  ', ', ') AS standard_name
    FROM pitch_aggregate
)
SELECT 
    NAME_BAT
    , LTRIM(RIGHT(standard_name, LEN(standard_name) - CHARINDEX(', ', standard_name))) AS FIRST_NAME
    , RTRIM(LEFT(standard_name, CHARINDEX(', ', standard_name) - 1)) AS LAST_NAME
    , LTRIM(RIGHT(standard_name, LEN(standard_name) - CHARINDEX(', ', standard_name))) + ' ' + RTRIM(LEFT(standard_name, CHARINDEX(', ', standard_name) - 1)) AS NAME_FULL
FROM processed_name
;

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