SQL使用SUBSTRING与CHARINDEX提取百分比时报长度参数错误如何解决

问题原因 #

你当前的写法默认字符串中一定存在至少两个-作为分隔符，当第二个分隔符变为_或者缺失时，第二个CHARINDEX会返回0，计算得到的SUBSTRING长度为负数，就会触发「Invalid length parameter passed to the LEFT or SUBSTRING function」报错。

稳妥解决方案（适配SQL Server，其他数据库可按逻辑调整函数） #

方案1：兼容异常分隔符+格式前置校验（兼容所有SQL Server版本） #

先把常见的异常分隔符统一替换为-，再提前校验字符串是否符合「至少2个分隔符」的要求，不符合格式的返回默认值不报错：

Percentage = ROUND(
    CASE 
        -- 校验替换后的字符串是否存在至少2个分隔符
        WHEN LEN(RCV1.ECCValue) - LEN(REPLACE(REPLACE(RCV1.ECCValue,'_','-'),'-','')) >= 2
        THEN SUBSTRING(
            REPLACE(RCV1.ECCValue,'_','-'),
            CHARINDEX('-', REPLACE(RCV1.ECCValue,'_','-')) + 1,
            CHARINDEX('-', REPLACE(RCV1.ECCValue,'_','-'), CHARINDEX('-', REPLACE(RCV1.ECCValue,'_','-')) + 1) 
                - CHARINDEX('-', REPLACE(RCV1.ECCValue,'_','-')) - 1
        )
        -- 不符合格式可自定义返回值，这里返回NULL，也可改0或者其他标记值
        ELSE NULL 
    END,
2)

方案2：JSON拆分法（更简洁，适配SQL Server 2016及以上版本） #

把字符串转为JSON数组直接取第二个元素，内置容错逻辑不会报错：

Percentage = ROUND(
    -- TRY_CAST遇到非法数值会自动返回NULL，不会触发报错
    TRY_CAST(
        JSON_VALUE('["' + REPLACE(REPLACE(RCV1.ECCValue,'_','-'),'-','","') + '"]','$[1]') 
        AS DECIMAL(18,2)
    ),
2)

补充说明 #

• 你可以根据实际遇到的异常分隔符类型，在REPLACE层添加更多替换规则，比如要兼容#、/作为第二个分隔符，就改成REPLACE(REPLACE(REPLACE(RCV1.ECCValue,'_','-'),'#','-'),'/','-')
• 如果使用的是MySQL、PostgreSQL等其他数据库，只需要把对应函数替换为数据库内置的函数即可：比如MySQL用LOCATE代替CHARINDEX，用JSON_EXTRACT代替JSON_VALUE，逻辑完全通用。

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