MS SQL地理距离排序时nvarchar转numeric出错的解决求助

我帮你梳理下问题根源并给出修正方案：

核心问题拆解 #

1. WHERE条件逻辑冲突：你写的longitude_deg LIKE '%[^0-9.]%'是在筛选包含非数字/小数点的行，这和isnumeric(...) = 1完全矛盾——后者是找能转成数字的有效行，前者却把这些行排除了，最终导致子查询里混入无效数据，触发转换报错。
2. 空值未明确过滤：虽然你说只剩空值，但空值会干扰ISNUMERIC判断，也会导致转换失败，需要明确排除。
3. CAST的安全性不足：MS SQL的ISNUMERIC偶尔会返回1但实际转换失败（比如带正负号的字符串），用TRY_CAST更稳妥，转换失败时返回NULL而非直接抛错。

修正后的查询语句 #

SELECT TOP 1 temp.Distance 
FROM ( 
    SELECT 
        (3956 * 2 * ASIN( 
            SQRT( 
                POWER(SIN((53.6349994 - ABS(TRY_CAST(latitude_deg AS NUMERIC(18,8)))) * PI() / 180 / 2), 2) 
                + COS(53.6349994 * PI()/180) 
                * COS(ABS(TRY_CAST(latitude_deg AS NUMERIC(18,8))) * PI()/180) 
                * POWER(SIN((10.0117336 - TRY_CAST(longitude_deg AS NUMERIC(18,8))) * PI()/180 / 2), 2) 
            )
        )) AS Distance 
    FROM Airport_Airports 
    WHERE 
        latitude_deg IS NOT NULL 
        AND longitude_deg IS NOT NULL 
        AND ISNUMERIC(latitude_deg) = 1 
        AND ISNUMERIC(longitude_deg) = 1 
        AND latitude_deg NOT LIKE '%[^0-9.]%' 
        AND longitude_deg NOT LIKE '%[^0-9.]%'
) AS temp 
WHERE temp.Distance < 50000 
ORDER BY temp.Distance ASC

关键改动说明 #

• 修正LIKE条件：把LIKE改成NOT LIKE，确保只保留由数字和小数点组成的有效经纬度字符串。
• 添加非空判断：明确排除空值行，避免空值参与转换和计算逻辑。
• 替换CAST为TRY_CAST：TRY_CAST在转换失败时返回NULL，这些NULL值会被外层temp.Distance < 50000条件过滤，保证排序时都是有效数值。
• 显式指定排序方向：加上ASC（默认也是升序，但显式书写更清晰），确保取到距离最近的机场。

额外优化建议 #

如果你的数据量较大，这种实时转换+计算的查询会很慢，建议后续把经纬度列改成NUMERIC类型，避免每次查询都要转换；另外可以考虑使用MS SQL的地理空间数据类型并创建空间索引，大幅提升距离查询的性能。

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