SQL Server:将数据类型 varchar 转换为数字时出错 [英] SQL Server : error converting data type varchar to numeric

问题描述

我有一张桌子:

Account_Code | Desc
503100       | account xxx
503103       | account xxx
503104       | account xxx
503102A      | account xxx
503110B      | account xxx

其中 Account_Code 是 varchar.

当我在下面创建查询时:

When I create a query below:

Select 
  cast(account_code as numeric(20,0)) as account_code,
  descr 
from account 
where isnumeric(account_code) = 1

通过返回在 account_code 列中具有有效数值的所有记录，它运行良好.

It runs well by returning all record that have a valid numeric value in account_code column.

但是当我尝试添加另一个选择时，嵌套到先前的 sql:

But when I try to add another select, nested to prior sql:

select account_code,descr 
from 
(
  Select cast(account_code as numeric(20, 0)) as account_code,descr 
  from account 
  where isnumeric(account_code) = 1
) a 
WHERE account_code between 503100 and 503105

查询将返回错误

将数据类型 varchar 转换为数字时出错.

Error converting data type varchar to numeric.

那里发生了什么?

如果 account_code 有效，我已经转换为数字，但查询似乎仍在尝试处理无效记录.

I have already converted to numeric if account_code valid, but it seems the query is still trying to process a non valid record.

我需要在查询中使用 BETWEEN 子句.

I need to use BETWEEN clause in my query.

推荐答案

SQL Server 2012 及更高版本

只需使用 Try_Convert 代替:

TRY_CONVERT 获取传递给它的值并尝试将其转换为指定的 data_type.如果转换成功，TRY_CONVERT 返回指定数据类型的值；如果发生错误，则返回 null.但是，如果您请求明确不允许的转换，则 TRY_CONVERT 会失败并显示错误.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

阅读有关 Try_Convert 的更多信息.

SQL Server 2008 及更早版本

处理这个问题的传统方法是用 case 语句保护每个表达式，这样无论何时计算它，它都不会产生错误，即使从逻辑上讲，CASE 语句似乎不需要.像这样:

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

但是，我喜欢在 SQL Server 2005 及更高版本中使用这样的策略:

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

这样做是策略性地将 Account_Code 值切换到 X 表内的 NULL 当它们不是数字时.我最初使用 CROSS APPLY 但正如 Mikael Eriksson 如此恰当地指出，这导致相同的错误，因为查询解析器遇到了完全相同的问题，即优化我强制表达式顺序的尝试(谓词下推打败了它).通过切换到 OUTER APPLY 它改变了操作的实际含义，以便 X.Account_Code could 包含 NULL 值在外部查询中，因此需要正确的评估顺序.

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

您可能有兴趣阅读 Erland Sommarskog 的 Microsoft Connect 请求 关于此评估订单问题.事实上，他称之为错误.

You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

这里还有其他问题，但我现在无法解决.

There are additional issues here but I can't address them now.

附言我今天有一个头脑风暴.我建议的传统方式"的替代方法是带有外部引用的 SELECT 表达式，它也适用于 SQL Server 2000.(我注意到自从学习 CROSS/OUTER APPLY 我也改进了旧 SQL Server 版本的查询功能——因为我通过 SELECT、ON 的外部引用"功能变得更加通用> 和 WHERE 子句！)

P.S. I had a brainstorm today. An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too--as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

它比 CASE 语句短很多.

这篇关于SQL Server:将数据类型 varchar 转换为数字时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！