Using SUM on nvarchar field

Question

Is it possible to use SUM function in a SQL query on a field that has been set as nvarchar but only contains numbers?

Answer 1

This is possible by casting:

SELECT SUM(CAST(NVarcharCol as int))

However, this will throw an exception is 1 or more records cannot be CAST to an 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.

If you are not able to change the data type, then the following WHERE clause would prevent exceptions from being thrown:

WHERE ISNUMERIC([NVarcharCol])=1

Answer 2

select sum (cast (myFiled as int)) from myTable