Arithmetic overflow error converting varchar to data type numeric?

Question

SELECT CASE Price WHEN '' THEN 0.0 ELSE Price END FROM Table

If price empty, it returns 0.0 but if column has value it gives me the error below

Arithmetic overflow error converting varchar to data type numeric.

How to change my code to handle this case?

Then how can it equal an empty string? I don't understand your CASE statement. — Joe
I'm selecting it from one table where price is string and inserting it in another table where the price is decimal — CodeHunter

Joe Joe · Accepted Answer · 2011-09-22T01:11:18

SELECT CASE Price WHEN '' THEN 0.0 ELSE CAST(Price AS DECIMAL(...)) END FROM Table

Implicit conversions will often take the smallest type possible, so when you hit a larger value it will fail. Explicitly convert/cast your value to the right type in your CASE/ELSE.

Arithmetic overflow error converting varchar to data type numeric?

1 Answers