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.
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屋!