Syntax error in sql caused by NULL data

Question

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"

why on earth would anyone store currency as a string? Everytime you do, a puppy dies. — Mitch Wheat
– Mitch Wheat, Commented Dec 28, 2013 at 2:28

Hart CO · Accepted Answer · 2013-12-28 02:35:41Z

5

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.

edited Dec 28, 2013 at 2:35

answered Dec 28, 2013 at 2:29

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Wheat Over a year ago

"but sadly we cannot always control the data we get" - no, but when you have it you can.

James Smith · Accepted Answer · 2013-12-28 02:30:41Z

2

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

answered Dec 28, 2013 at 2:30

James Smith

1,1221 gold badge11 silver badges14 bronze badges

Collectives™ on Stack Overflow

Syntax error in sql caused by NULL data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related