nvarchar转integer转换错误排查：嵌套CAST函数是否存在问题？

你的嵌套CAST函数语法上其实没毛病，但问题出在两个核心点——SQL查询的执行顺序逻辑和数据质量，这才是触发转换错误的根源。

为什么会报错？ #

1. 查询优化器的执行顺序陷阱
   很多人会默认CTE会先把所有行处理完生成zip_clean，再执行WHERE过滤，但SQL的查询优化器不会严格按你写的代码顺序执行。它可能会重写查询，把WHERE里的条件提前到CTE的处理流程中，导致某些还没完成转换的行就被拿来和整数对比，直接触发nvarchar转int的错误。

2. 未覆盖的脏数据
   你的清理逻辑只处理了空格、短横线，但如果Zip_Code里还有其他非数字字符（比如字母、特殊符号，像12A34、12!34这类），哪怕经过你的替换处理后，结果还是非数字字符串，转int自然会报错。

修正后的解决方案 #

最稳妥的办法是用TRY_CAST（SQL Server 2012及以上支持）或者TRY_CONVERT，这两个函数在转换失败时不会抛出错误，而是返回NULL，这样查询能正常执行，同时自动过滤掉无法转换的行：

with zips as ( 
    select 
        id,
        TRY_CAST(replace(replace(left(ltrim(Zip_Code),5), '-', ''), char(32), '0') as int) as zip_clean 
    from table1
) 
select * from zips where zip_clean in (19116, 94595, 60062)

如果你用的是更早版本的SQL Server，没法用TRY_CAST，可以先通过ISNUMERIC函数筛选出能转换的行，再做CAST：

with zips as ( 
    select 
        id,
        cast(replace(replace(left(ltrim(Zip_Code),5), '-', ''), char(32), '0') as int) as zip_clean 
    from table1
    -- 先筛选出处理后为纯数字的行
    where ISNUMERIC(replace(replace(left(ltrim(Zip_Code),5), '-', ''), char(32), '0')) = 1
) 
select * from zips where zip_clean in (19116, 94595, 60062)

额外提醒 #

你替换空格为0的逻辑需要确认是否符合业务需求：比如原Zip_Code是12 34（中间有空格），处理后会变成12034，如果这不是你想要的结果，可能需要调整清理规则哦。

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