如何在Snowflake的CASE语句中忽略非数值避免报错

要解决非数值字段导致的计算报错，核心是先对字段进行安全的数值转换，转换失败时返回预设值（如NULL或0），避免触发类型错误。以下分通用方案和低版本数据库兼容方案说明：

通用方案（支持TRY_CAST的数据库：MySQL 8.0+、PostgreSQL 12+、SQL Server 2012+等） #

使用TRY_CAST函数尝试转换字段为数值类型，转换失败时自动返回NULL，不会报错。调整后的代码如下：

CASE exp_pg_partners.DISCOUNT
    WHEN 'FALSE' THEN TRY_CAST(pp.fee AS DECIMAL(18,2))
    WHEN 'TRUE' THEN 
        TRY_CAST(pp.fee AS DECIMAL(18,2)) - 
        (TRY_CAST(pp.fee AS DECIMAL(18,2)) * (TRY_CAST(exp_pg_partners.DISCOUNT_PERCENTAGE AS DECIMAL(5,2)) / 100))
    ELSE NULL -- 处理DISCOUNT既非TRUE也非FALSE的情况
END AS Annual_Tuition_Fees_in_Local_Currency
FROM ...

说明： #

• DECIMAL(18,2)和DECIMAL(5,2)是数值精度设置，可根据业务数据的实际范围调整（比如金额类常用18位整数+2位小数，折扣百分比用5位整数+2位小数足够）。
• 若需要将转换失败的情况默认设为0，只需把TRY_CAST(...)替换为COALESCE(TRY_CAST(...), 0)。

低版本数据库兼容方案（如MySQL 5.x，不支持TRY_CAST） #

通过正则判断字段是否为有效数值，再进行转换：

CASE exp_pg_partners.DISCOUNT
    WHEN 'FALSE' THEN 
        CASE WHEN pp.fee REGEXP '^[0-9]+(\.[0-9]+)?$' THEN CAST(pp.fee AS DECIMAL(18,2)) ELSE NULL END
    WHEN 'TRUE' THEN 
        CASE 
            WHEN pp.fee REGEXP '^[0-9]+(\.[0-9]+)?$' AND exp_pg_partners.DISCOUNT_PERCENTAGE REGEXP '^[0-9]+(\.[0-9]+)?$' 
            THEN CAST(pp.fee AS DECIMAL(18,2)) - (CAST(pp.fee AS DECIMAL(18,2)) * (CAST(exp_pg_partners.DISCOUNT_PERCENTAGE AS DECIMAL(5,2)) / 100))
            ELSE NULL 
        END
    ELSE NULL
END AS Annual_Tuition_Fees_in_Local_Currency
FROM ...

说明： #

• 正则表达式^[0-9]+(\.[0-9]+)?$用于匹配整数或小数格式的有效数值。
• 同样可根据业务需求，把ELSE NULL改为ELSE 0，设置转换失败时的默认值。

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