如何修复SQL中LEFT()函数被传入负数值的报错问题

解决方案 #

核心问题是当PATINDEX未匹配到数字时会返回0，减1后得到的-1属于LEFT函数不支持的负长度参数，我们可以通过条件判断把负长度替换为0即可解决报错问题，同时可以优化原有嵌套逻辑适配更多异常邮编格式。

优化后代码（兼容SQL Server 2012及以上版本） #

DECLARE @Postcode nVARCHAR (10) = 'ab124th'
-- 提取空格前的前缀部分，无空格则取完整字符串
DECLARE @PostcodePrefix NVARCHAR(10) = LEFT(@Postcode, ISNULL(NULLIF(CHARINDEX(' ', @Postcode), 0) - 1, LEN(@Postcode)))

SELECT
    RTRIM(
        LEFT(
            @PostcodePrefix,
            -- 未匹配到数字时返回0，避免负长度报错
            IIF(PATINDEX('%[0-9]%', @PostcodePrefix) > 0, PATINDEX('%[0-9]%', @PostcodePrefix) - 1, 0)
        )
    )

低版本兼容写法（适配SQL Server 2012之前版本） #

把IIF替换为CASE表达式即可，逻辑完全一致：

DECLARE @Postcode nVARCHAR (10) = 'ab124th'
DECLARE @PostcodePrefix NVARCHAR(10) = LEFT(@Postcode, ISNULL(NULLIF(CHARINDEX(' ', @Postcode), 0) - 1, LEN(@Postcode)))

SELECT
    RTRIM(
        LEFT(
            @PostcodePrefix,
            CASE WHEN PATINDEX('%[0-9]%', @PostcodePrefix) > 0 THEN PATINDEX('%[0-9]%', @PostcodePrefix) - 1 ELSE 0 END
        )
    )

逻辑说明 #

• 新增@PostcodePrefix变量简化重复嵌套计算，同时处理无空格的异常格式：如果邮编中没有空格，直接取完整字符串作为前缀处理
• 对PATINDEX的计算结果做判断：只有匹配到数字时才减1，否则返回0，避免传入负长度触发LEFT函数报错

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