SQL 在 nvarchar 字段上使用 SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8788211/
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
Using SUM on nvarchar field
提问by Darren Cook
Is it possible to use SUM function in a SQL query on a field that has been set as nvarchar but only contains numbers?
是否可以在已设置为 nvarchar 但仅包含数字的字段的 SQL 查询中使用 SUM 函数？
回答by Curt
This is possible by casting:
这可以通过铸造实现：
SELECT SUM(CAST(NVarcharCol as int))
However, this will throw an exception is 1 or more records cannot be CASTto an int.
但是，这将引发异常，因为 1 个或多个记录不能CAST到一个int.
If your column is only ever going to have numerical values, I would recommend changing the data type to int to save future problems.
如果您的列只有数值，我建议将数据类型更改为 int 以节省未来的问题。
If you are not able to change the data type, then the following WHEREclause would prevent exceptions from being thrown:
如果您无法更改数据类型，则以下WHERE子句将防止抛出异常：
WHERE ISNUMERIC([NVarcharCol])=1
回答by Royi Namir
select sum (cast (myFiled as int)) from myTable
回答by shuvo sarker
You Need Casting,Try this
你需要铸造，试试这个
SUM(COALESCE(CoumnName, 0))
回答by Oleg Dok
You have to cast the varchar to numeric type before using SUM on it, instead you'll have an error:
在对它使用 SUM 之前，您必须将 varchar 转换为数字类型，而是会出现错误：
Msg 8117, Level 16, State 1, Line 6 Operand data type varchar is invalid for sum operator.
消息 8117，级别 16，状态 1，第 6 行操作数数据类型 varchar 对求和运算符无效。
If it contains only numeric data - then what is the point of storing the data in varchar?
如果它只包含数字数据 - 那么将数据存储在 varchar 中有什么意义？
回答by lazydeveloper
use SUM(COALESCE(CoumnName, 0)) I recommend ,You should change data type to number.Its good practice.
使用 SUM(COALESCE(CoumnName, 0)) 我推荐，您应该将数据类型更改为数字。这是一个很好的做法。
回答by Eduard Enriquez
if you want to add two nvarchar columns. column TestTotal (nvarchar), column TestFailed (nvarchar), column TestPassed (nvarchar)
如果要添加两个 nvarchar 列。列 TestTotal (nvarchar)、列 TestFailed (nvarchar)、列 TestPassed (nvarchar)
UPDATE [your_db].[dbo].[your_table]
SET your_table.TestTotal = Convert(nvarchar, cast (your_table.TestFailed as int) + cast (your_table.TestPassed as int))
WHERE TestTotal='your_condition';
