SQL使用REPLACE拆分JOBNUMBER字段报错，如何正确提取CONTRACTNUMBER？

问题根因 #

1. 代码存在拼写错误：将JOBNUMBER误写为JOBNUMEBR，会直接触发列不存在的报错。
2. 核心逻辑错误是滥用REPLACE函数：REPLACE会替换字符串中所有匹配的子串，而非仅替换第一个出现的子串。
   以报错的8_8_000为例：

• 提取的第一个子串substring(BD.JOBNUMBER, 1, charindex('_', BD.JOBNUMBER))结果为8_
• 执行REPLACE(BD.JOBNUMBER, '8_', '')时，会把原字符串中两处8_全部替换为空，最终得到000，而非预期的8_000，后续CHARINDEX找不到下划线返回0，LEFT函数传入负数长度就触发了报错。

正确解法 #

不要用REPLACE做前缀移除，直接通过CHARINDEX的第三个参数指定搜索起始位置，定位到第二个下划线的位置后直接截取即可，不会受到前后段内容是否重复的影响：

SELECT 
    BD.BTCHID,
    BD.JOBNUMBER AS JOBNUMBER,
    SUBSTRING(
        BD.JOBNUMBER,
        -- 第一个下划线的下一位作为截取起始点
        CHARINDEX('_', BD.JOBNUMBER) + 1,
        -- 第二个下划线位置减去第一个下划线位置再减1，得到中间内容的长度
        CHARINDEX('_', BD.JOBNUMBER, CHARINDEX('_', BD.JOBNUMBER) + 1) - CHARINDEX('_', BD.JOBNUMBER) - 1
    ) AS CONTRACTNUMBER
FROM [dbo].[BATCH_DETAILS] BD;

如果需要兼容部分格式不符合a_b_c规则的JOBNUMBER，避免触发报错，可以增加判断逻辑做健壮性优化：

SELECT 
    BD.BTCHID,
    BD.JOBNUMBER AS JOBNUMBER,
    CASE WHEN 
        CHARINDEX('_', BD.JOBNUMBER) > 0 
        AND CHARINDEX('_', BD.JOBNUMBER, CHARINDEX('_', BD.JOBNUMBER) + 1) > 0
    THEN
        SUBSTRING(
            BD.JOBNUMBER,
            CHARINDEX('_', BD.JOBNUMBER) + 1,
            CHARINDEX('_', BD.JOBNUMBER, CHARINDEX('_', BD.JOBNUMBER) + 1) - CHARINDEX('_', BD.JOBNUMBER) - 1
        )
    ELSE NULL END AS CONTRACTNUMBER
FROM [dbo].[BATCH_DETAILS] BD;

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