CODEWITHSUNDEEP
sql
Darren Cook
Darren Cook

Reputation: 721

Using SUM on nvarchar field

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

Upvotes: 5

Views: 61965

Answers (6)

Eduard Enriquez
Eduard Enriquez

Reputation: 1

if you want to add two nvarchar columns. column TestTotal (nvarchar), column TestFailed (nvarchar), column 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';

Upvotes: 0

lazydeveloper
lazydeveloper

Reputation: 961

use SUM(COALESCE(CoumnName, 0)) I recommend ,You should change data type to number.Its good practice.

Upvotes: 0

shuvo sarker
shuvo sarker

Reputation: 881

You Need Casting,Try this

SUM(COALESCE(CoumnName, 0))

Upvotes: 2

Curtis
Curtis

Reputation: 103348

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

Upvotes: 18

Oleg Dok
Oleg Dok

Reputation: 21756

You have to cast the varchar to numeric type before using SUM on it, instead you'll have an error:

Msg 8117, Level 16, State 1, Line 6 Operand data type varchar is invalid for sum operator.

If it contains only numeric data - then what is the point of storing the data in varchar?

Upvotes: 1

Royi Namir
Royi Namir

Reputation: 148524

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

Upvotes: 3

Related Questions