将数据类型nvarchar转换为数字时出错 - SQL Server
我想在我的数据库中取一列的平均值。该列是AMOUNT
,它存储为NVARCHAR(300),null
。将数据类型nvarchar转换为数字时出错 - SQL Server
当我尝试将其转换为数值我得到以下错误:
Msg 8114, Level 16, State 5, Line 1
Error converting datatype NVARCHAR to NUMBER
这是我现在所拥有的。
SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1
AND Reimbursement IS NOT NULL
您会认为您的代码可行。但是,SQL Server不保证SELECT
的转换发生前WHERE
子句筛选数据库。在我看来这是一个错误。在微软看来,这是一项优化功能。
因此,您的WHERE
不能保证工作。即使使用CTE也不能解决问题。
最好的解决办法是在SQL Server 2012+ TRY_CONVERT()
可供选择:
SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;
在早期版本中,你可以使用CASE
。该CASE
确实保证条款的顺序排序,所以:
SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
THEN CONVERT(DECIMAL(18,2), Reimbursement))
END)
FROM Database;
因为AVG()
忽略NULL
值,该WHERE
是没有必要的,但你可以包括,如果你喜欢。
alter database add Reimbursement_Value as
(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
THEN CONVERT(DECIMAL(18,2), Reimbursement))
END);
然后,你可以写代码:
select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;
Quote from 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
select isnumeric('+')---1
select isnumeric('$')---1
所以要尽量添加避免非数字号码与您的输出中搞乱..
WHERE Reimbursement NOT LIKE '%[^0-9]%'
如果您对SQLServer的2012年,你可以尝试使用TRY_Convert输出为空转换失败..
SELECT AVG(try_convert(DECIMAL(18,2),Reimbursement))
from
table
'try_convert()'是正确的答案。 –
我在猜测,既然是Nvarchar,你会在那里用'$','。'或者一个(,)找到一些值。我想这likt运行查询:
SELECT Amount
FROM database
WHERE Amount LIKE '%$%' OR
Amount LIKE '%.%' OR
Amount LIKE '%,%'
看看你会得到什么,我猜你会得到一些返回的行,然后更新这些行,然后再试一次。
当前您的查询会提取所有不是全部数字的数字,这也是它失败的原因。请尝试运行此操作:
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
嗨@Wes Palmer我跑了第一线,我收到并输出像这样。 $ 11,162 $ 11,162 $ 11,162 $ 11,162 $ 11,162 $ 10,194可能是因为当我尝试将其转换为INT时,有一个美元符号导致我的所有值? –
是的美元符号和逗号分隔数以千计的数以千计也会导致它失败,因为它存储为纯文本。因此,您可能必须将这些值更新为一个数字,然后使用您的脚本。 –
好像你在该列中一些非数值
最后,你可以通过使用计算列简化代码... – jarlh
您正在使用哪个版本的SQLServer? – TheGameiswar