sql-server – SQL Server：将数据类型varchar转换为数字的错误

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

其中Account_Code是一个varchar。

当我在下面创建一个查询：

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

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

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

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

该查询将返回一个错误

Error converting data type varchar to numeric.

那里发生了什么？

如果account_code有效，我已经转换成数字，但似乎查询仍然在尝试处理一个无效的记录。

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

解决方法

只需使用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.

Read more about Try_Convert。

SQL Server 2008和更早版本

处理这种情况的传统方式是使用case语句来保护每个表达式，以便不管何时被评估，它不会创建错误，即使逻辑上似乎不需要CASE语句。这样的事情

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及以上版本的策略：

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可以在外部查询中包含NULL值，因此需要适当的评估顺序。

您可能有兴趣阅读Erland Sommarskog’s Microsoft Connect request关于此评估订单问题。实际上他称之为错误。

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

附：今天我头脑风暴我建议的“传统方法”的替代方法是具有外部引用的SELECT表达式，这也可以在SQL Server 2000中使用。(我注意到，自从学习CROSS / OUTER APPLY以来，我已经改进了使用旧SQL的查询功能服务器版本也是如此，我使用SELECT，ON和WHERE子句的“外部引用”功能变得更加通用！)

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

它比CASE语句短得多。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!