使用CASE结合SUBSTRING查询并与数字相乘时出现SQL错误

原查询存在的问题 #

• CASE逻辑错误：未先判断第6位字符是否为数字，直接做数值比较会导致非数字字符的判断失效；同时用数字0和字符类型的子串比较，会引发类型不匹配问题。
• 多余拼接操作：结尾的+ substring(PostalCode,6,1)会将CASE结果与原字符拼接，导致输出偏离预期（比如预期100会变成1000）。
• 文本不一致：预期结果是NoDigit，但原语句写的是NotDigit，两者需要统一。

修正后的SQL语句 #

SELECT
    LEFT(PostalCode, 2) AS area,
    CASE
        -- 优先判断第6位是否为数字
        WHEN ISNUMERIC(SUBSTRING(PostalCode, 6, 1)) = 0 THEN 'NoDigit'
        WHEN SUBSTRING(PostalCode, 6, 1) = '0' THEN '100'
        ELSE CAST((CAST(SUBSTRING(PostalCode, 6, 1) AS INT) * 100) AS VARCHAR)
    END AS [location]
FROM province_table

样本数据验证结果 #

对应你的测试数据，输出结果如下：

完全匹配你给出的预期输出。

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