问SQL Server :将数据类型varchar转换为numeric时出错

EN

SQL Server 2012和更高版本的

只需使用Try_Convert即可：

TRY_CONVERT接受传递给它的值，并尝试将其转换为指定的data_type。如果转换成功，则TRY_CONVERT将返回值作为指定的data_type；如果发生错误，则返回null。但是，如果您请求的转换是明确不允许的，则TRY_CONVERT将失败并显示错误。

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。他实际上称它为bug。

这里还有其他问题，但我现在不能解决它们。

另外，我今天有了一个头脑风暴。我建议的“传统方法”的另一种方法是带有外部引用的SELECT表达式，这种方法也适用于SQL Server2000。(我注意到，自从学习CROSS/OUTER APPLY以来，我也用较早的SQL Server版本改进了查询能力--因为我越来越能使用SELECT、ON和WHERE子句的“外部引用”功能！)

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在运行WHERE子句中的筛选器之前不会尝试对numeric(20,0)执行CONVERT。

而且，即使它这样做了，ISNUMERIC也是不够的，因为它将£和1d4识别为数字，这两者都不能转换为numeric(20,0)。(*)

将其拆分为两个单独的查询，第一个查询过滤结果并将其放入临时表或表变量中，第二个查询执行转换。(子查询和CTE不足以阻止优化器在过滤器之前尝试转换)

对于您的筛选器，可能要使用account_code not like '%[^0-9]%'而不是ISNUMERIC。

(*) ISNUMERIC回答了没有人(据我所知)想要问的问题--“这个字符串能转换成任何数值数据类型吗--我不在乎是哪种？”--很明显，大多数人想问的是“这个字符串能转换成x吗？”其中x是特定的目标数据类型。

如果您运行的是SQL Server 2012或更高版本，还可以使用新的TRY_PARSE()函数：

返回表达式的结果，转换为请求的数据类型；如果SQL Server中的强制转换失败，则返回null。仅在将字符串转换为日期/时间和数字类型时使用TRY_PARSE。

或TRY_CONVERT/TRY_CAST

如果转换成功，则

返回转换为指定数据类型的值；否则返回null。