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

I have a table:

我有一张桌子：

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

Where Account_Code is a varchar.

其中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

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

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

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

但是当我尝试添加另一个select时，嵌套到先前的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

the query will return an error

查询将返回错误

Error converting data type varchar to numeric.

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

What is happening there?

那里发生了什么？

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

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

I need to use BETWEEN clause in my query.

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

5 个解决方案

#1

SQL Server 2012 and Later

SQL Server 2012及更高版本

Just use Try_Convert instead:

只需使用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获取传递给它的值并尝试将其转换为指定的data_type。如果转换成功，则TRY_CONVERT返回值作为指定的data_type;如果发生错误，则返回null。但是，如果您请求明确不允许的转换，则TRY_CONVERT将失败并显示错误。

Read more about Try_Convert.

了解有关Try_Convert的更多信息。

SQL Server 2008 and Earlier

SQL Server 2008和早期版本

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:

处理此问题的传统方法是使用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

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

但是，我喜欢在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

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.

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

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

您可能有兴趣阅读有关此评估订单问题的Erland Sommarskog的Microsoft Connect请求。他实际上称之为一个错误。

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

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

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表达式，它也适用于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]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It's a lot shorter than the CASE statement.

它比CASE声明短得多。

#2

There's no guarantee that SQL Server won't attempt to perform the CONVERT to numeric(20,0) before it runs the filter in the WHERE clause.

在运行WHERE子句中的过滤器之前，无法保证SQL Server不会尝试将CONVERT执行到数字（20,0）。

And, even if it did, ISNUMERIC isn't adequate, since it recognises £ and 1d4 as being numeric, neither of which can be converted to numeric(20,0).(*)

而且，即使它确实如此，ISNUMERIC也不够，因为它将£和1d4识别为数字，两者都不能转换为数字（20,0）。（*）

Split it into two separate queries, the first of which filters the results and places them in a temp table or table variable, the second of which performs the conversion. (Subqueries and CTEs are inadequate to prevent the optimizer from attempting the conversion before the filter)

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

For your filter, probably use account_code not like '%[^0-9]%' instead of ISNUMERIC.

对于您的过滤器，可能使用account_code而不是'％[^ 0-9]％'而不是ISNUMERIC。

(*) ISNUMERIC answers the question that no-one (so far as I'm aware) has ever wanted to ask - "can this string be converted to any of the numeric datatypes - I don't care which?" - when obviously, what most people want to ask is "can this string be converted to x?" where x is a specific target datatype.

（*）ISNUMERIC回答了一个问题：没有人（据我所知）曾经想问过 - “这个字符串是否可以转换为任何数值数据类型 - 我不关心哪个？” - 很明显，大多数人想要问的是“这个字符串可以转换成x吗？”其中x是特定的目标数据类型。

#3

If you are running SQL Server 2012 you can also use the new TRY_PARSE() function:

如果您运行的是SQL Server 2012，还可以使用新的TRY_PARSE（）函数：

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server 2012. Use TRY_PARSE only for converting from string to date/time and number types.

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

#4

I think the problem is not in sub-query but in WHERE clause of outer query. When you use

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

WHERE account_code between 503100 and 503105

SQL server will try to convert every value in your Account_code field to integer to test it in provided condition. Obviously it will fail to do so if there will be non-integer characters in some rows.

SQL Server将尝试将Account_code字段中的每个值转换为整数，以便在提供的条件下对其进行测试。显然，如果某些行中存在非整数字符，则无法执行此操作。

#5

thanks, try this instead

谢谢，试试吧

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

I'm happy to help you

我很乐意帮助你

#1