主页 / dba / 问题 / 28040
Accepted
hexparrot
Asked: 2012-11-02 14:27:16 +0800 CST

获取“将 nvarchar 值转换为数据类型 int 时转换失败。”

  • 772

我有一个 SQL Server 表配对邮政编码和纬度/经度几何，如下所示

ID  ZIP5    geom
1   356HH   0xE610000001044E00....FFFFFFFF0000000003ID
2   35677   0xE6100000010404000000068....000003

我遇到的问题是，由于 ZIP5 字段包含字符，我似乎无法从一种类型转换为另一种类型。

我尝试过的是一种方法，其中我只提取完全整数友好的记录：

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CONVERT(int, [ZIP5]) AS zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ISNUMERIC([ZIP5]) = 1
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

我假设由于子查询返回了一个INT类型，所以在外部执行普通比较运算符绝对没有问题WHERE。执行此查询时出现以下错误：

消息 245，级别 16，状态 1，行 1
将 nvarchar 值“356HH”转换为数据类型 int 时转换失败。

省略WHEREexternal ，我在 ZIP5 列中得到一个包含 100% 整数的完整结果集。使用 external WHERE，它似乎超越了前一个CONVERT并且转换失败。

作为一些额外的说明：

  1. 这个查询的性质是好的，没有得到有字母的行。
  2. 将 HH（或任何其他双字符对）替换为数字零也是完全可以的（但是尝试过REPLACE但STUFF无济于事）
  3. 我已经尝试过 TRY/CATCH，但它在语句中似乎不起作用WHERE——当更广泛地捕获整个查询时，try/catch 最终将返回一个空集。

如果不制作临时表 - 表变量，哪种方法是最好的解决方法？

sql-server cast

2 个回答

  1. Best Answer

    以这种方式编写您的查询。例如，它包括一个更好的整数测试和转储 ISNUMERIC，它为“-.”返回 1。

    SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

    看到这个连接项目

    SQL Server 可以按照它决定优化的顺序自由地评估 WHERE/SELECT 子句。未物化的视图或派生表可以很容易地从外部查询扩展到。

    SQL Server 正在编译的实际上是一个看起来像这样的查询

    SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

    你可以检查你原来的查询计划，但我从结构上的猜测是 WHERE 子句使用了CONVERT(int, [ZIP5])两次表达式，因此在检索过程中简化表达式的解析（计算到结果）是有意义的表中的数据。这会将 SELECT 子句的处理放在 WHERE 之前，因此您ISNUMERIC() = 1永远没有机会过滤坏鸡蛋。

    它是by-design。

    • 2

  2. 这并不是对您问题的严格回答，但我会从您的问题中退后一步，思考您要解决的问题。

    为什么要将邮政编码存储为整数？根据我的经验，没有充分的理由将您的邮政编码存储为字符串以外的任何内容。

    您说您可以忽略其中带有字母的值，但即使是严格的数字拉链也不总是有效。例如，02345 将变为 2345。除此之外，如果您决定添加外部数据整数将根本不起作用，您将需要做大量工作来修复它。例如，加拿大邮政编码如下所示K1A 0B1。显然，除了字符串数据类型之外，它不适合任何东西。更不用说它不适合 CHAR(5)，这是邮政编码的另一个常见数据类型错误。

    最后，将它们存储为整数没有任何好处。您不会对它们进行总计、平均或执行标准偏差，对吗？那么，为什么还要麻烦将它们存储为数字呢？int 将比 char(5) 占用更少的空间（或者更好的是 varchar(10)），但它节省的空间非常小，并且不能弥补不良数据。

    • 2

相关问题