SQL Server：将数据类型varchar转换为numeric时出错。

Question

我有一个表格：

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, 0)) as account_code,descr 
  from account 
  where isnumeric(account_code) = 1
) a 
WHERE account_code between 503100 and 503105

查询将返回错误。

转换数据类型varchar为numeric时出错。

发生了什么？

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

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

- user1947840

---

SQL Server 的版本是什么？ - ErikE

Answer 1

SQL Server 2012及之后版本

只需使用Try_Convert函数即可：

TRY_CONVERT将传递给它的值尝试转换为指定的数据类型。如果转换成功，TRY_CONVERT将该值作为指定的数据类型返回；如果出现错误，则返回null。但是，如果您请求明确不允许的转换，则TRY_CONVERT会失败并显示错误。

了解更多关于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

这个操作的作用是在X表中，当Account_Code的值不是数字时，将其策略性地更改为NULL。我最初使用了CROSS APPLY，但正如Mikael Eriksson所指出的那样，这导致了相同的错误，因为查询解析器遇到了完全相同的问题来优化我的尝试（谓词下推打败了它）。通过转换为OUTER APPLY，它改变了操作的实际意义，使得在外部查询中X.Account_Code可以包含NULL值，因此需要正确的评估顺序。

您可能会有兴趣阅读Erland Sommarskog的Microsoft Connect请求，涉及此评估顺序问题。他实际上称之为一个错误。

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

补充一句，我今天有个灵感。替代我建议的“传统方式”的方法是带有外部引用的SELECT表达式，这也适用于 SQL Server 2000。（我注意到，自从学会了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语句要短得多。

- ErikE

---

查询对你有效吗？除非我将“cross apply”更改为“outer apply”，否则对我无效。(SQL Server 2012) - Mikael Eriksson

我的意思是你目前的版本不起作用。查询计划执行了一个表扫描，检查每一行。如果你改成外部应用程序，它会在转换之前过滤掉错误的行。SQL Fiddle - Mikael Eriksson

Answer 2

无法保证SQL Server不会在运行WHERE子句中的筛选器之前尝试将CONVERT转换为numeric(20,0)。

即使它这样做，使用ISNUMERIC也是不足够的，因为它会将£和1d4识别为数字，但这两者都无法转换为numeric(20,0)。（*）

将其拆分为两个单独的查询，第一个筛选结果并将其放入临时表或表变量中，第二个执行转换。（子查询和CTE无法防止优化器在筛选器之前尝试进行转换）

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

---

（*）ISNUMERIC回答了无人想要问的问题 -“此字符串是否可转换为任何数值数据类型 - 我不关心是哪种？” - 当显然，大多数人想问的是，“此字符串是否可以转换为x？”其中x是一个特定的目标数据类型。

- Damien_The_Unbeliever

Answer 3

如果你正在运行SQL Server 2012或更高版本，你也可以使用新的TRY_PARSE()函数：

返回表达式的结果，转换为所请求的数据类型，如果在SQL Server中转换失败，则返回null。只有在从字符串到日期/时间和数字类型进行转换时才使用TRY_PARSE。

或者TRY_CONVERT/TRY_CAST：

如果转换成功，则返回一个值强制转换为指定的数据类型; 否则返回null。

- mprost

Answer 4

我认为问题不在子查询中，而是在外部查询的WHERE子句中。 当你使用

WHERE account_code between 503100 and 503105

SQL Server会尝试将您的Account_code字段中的每个值转换为整数以在提供的条件下测试它。显然，如果某些行中存在非整数字符，它就无法这样做。

- Blindfold

Answer 5

感谢您，尝试使用这个替代方案。

Select 
  STR(account_code) as account_code_Numeric,
  descr 
from account 
where  STR(account_code) = 1

我很高兴能够帮助你

- Majed jemmieh

Answer 6

如果您在where子句中使用varchar，您需要为where子句中使用的值使用撇号。因此，我认为您的where子句值需要更正如下：

WHERE account_code between '503100' and '503105'

- Sankha Rathnayake