CODEWITHSUNDEEP
sqlsql-servert-sql
newbie
newbie

Reputation: 117

Syntax error in sql caused by NULL data

i have field data that consist of NULL. I try to get SUM data by making NULL data as 0.00.

I use

select Sum(cast(ISNULL (amount,0) as money)) from t

here the demo

How to avoid this error "Cannot convert a char value to money. The char value has incorrect syntax"

Upvotes: 1

Views: 1612

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Your NULL is not a null value, but rather a string with the word 'NULL' in it:

SQL Fiddle

When you change it to a real NULL as per the demo, this will work without an ISNULL() since aggregates ignore NULL values:

SELECT SUM(CAST(amount as MONEY)) 
FROM t

If you need to work around that string value, use REPLACE() instead of ISNULL():

SELECT SUM(CAST(REPLACE(amount,'NULL',0) as MONEY)) 
FROM t

Best practice would of course be to not store numeric as strings, but sadly we cannot always control the data we get.

Upvotes: 5

James Smith
James Smith

Reputation: 1122

In your demo, you don't actually have a null value in the last record, you have a character string called 'NULL'. Change it to an actual null value and it should work

Upvotes: 2

Related Questions