将数据类型 nvarchar 转换为数字时出错 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39022109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error converting data type nvarchar to numeric - SQL Server
提问by Pat Doyle
I am trying to take an average of a column in my database. The column is AMOUNTand it is stored as NVARCHAR(300),null.
我正在尝试取数据库中一列的平均值。该列是AMOUNT，它存储为NVARCHAR(300),null.
When I try to convert it to a numeric value I get the following error:
当我尝试将其转换为数值时，出现以下错误：
Msg 8114, Level 16, State 5, Line 1
Error converting datatype NVARCHAR to NUMBER
消息 8114，级别 16，状态 5，第 1
行将数据类型 NVARCHAR 转换为 NUMBER 时出错
Here is what I have right now.
这是我现在所拥有的。
SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1
AND Reimbursement IS NOT NULL
回答by Gordon Linoff
You would think that your code would work. However, SQL Server does not guarantee that the WHEREclause filters the database before the conversion for the SELECTtakes place. In my opinion this is a bug. In Microsoft's opinion, this is an optimization feature.
你会认为你的代码会起作用。但是，SQL Server 不保证WHERE子句在转换SELECT发生之前过滤数据库。在我看来，这是一个错误。在微软看来，这是一项优化功能。
Hence, your WHEREis not guaranteed to work. Even using a CTE doesn't fix the problem.
因此，您WHERE不能保证工作。即使使用 CTE 也不能解决问题。
The best solution is TRY_CONVERT()available in SQL Server 2012+:
TRY_CONVERT()SQL Server 2012+ 中提供了最佳解决方案：
SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;
In earlier versions, you can use CASE. The CASEdoesguarantee the sequential ordering of the clauses, so:
在早期版本中，您可以使用CASE. TheCASE确实保证了子句的顺序，所以：
SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
THEN CONVERT(DECIMAL(18,2), Reimbursement))
END)
FROM Database;
Because AVG()ignores NULLvalues, the WHEREis not necessary, but you can include it if you like.
因为AVG()忽略NULL值，所以WHERE不是必需的，但如果您愿意，可以包含它。
Finally, you could simplify your code by using a computed column:
最后，您可以使用计算列来简化代码：
alter database add Reimbursement_Value as
(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
THEN CONVERT(DECIMAL(18,2), Reimbursement))
END);
Then you could write the code as:
然后你可以把代码写成：
select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;
回答by TheGameiswar
引用自 MSDN...
ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney
对于某些不是数字的字符，例如加号 (+)、减号 (-) 和美元符号 ($) 等有效货币符号，ISNUMERIC 返回 1。有关货币符号的完整列表，请参阅 money 和 smallmoney
select isnumeric('+')---1
select isnumeric('$')---1
so try to add to avoid non numeric numbers messing with your ouput..
所以尝试添加以避免非数字数字弄乱您的输出..
WHERE Reimbursement NOT LIKE '%[^0-9]%'
If you are on SQLServer 2012,you could try using TRY_Convert which outputs null for conversion failures..
如果您使用的是 SQLServer 2012，您可以尝试使用 TRY_Convert，它在转换失败时输出 null。
SELECT AVG(try_convert( DECIMAL(18,2),Reimbursement))
from
table
回答by Wes Palmer
I am guessing that since it is Nvarchar you are going to find some values in there with a '$','.', or a (,). I would run a query likt this:
我猜因为它是 Nvarchar，所以你会在其中找到一些带有 '$'、'.' 或 (,) 的值。我会像这样运行一个查询：
SELECT Amount
FROM database
WHERE Amount LIKE '%$%' OR
Amount LIKE '%.%' OR
Amount LIKE '%,%'
See what you get and my guess you will get some rows returned and then update those rows and try it again.
看看你得到了什么，我猜你会得到一些返回的行，然后更新这些行并再试一次。
Currently your query would pull all numbers that are not all numeric which is a reason why it is failing too. Instead try running this:
目前，您的查询会提取所有不是数字的数字，这也是它失败的原因。而是尝试运行这个：
SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
--Changed ISNUMERIC() = to 0 for true so it will only pull numeric numbers.
WHERE ISNUMERIC(Reimbursement) = 0 and Reimbursement IS NOT NULL
