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
– Joe, Commented Sep 22, 2011 at 1:06
I'm selecting it from one table where price is string and inserting it in another table where the price is decimal — CodeHunter
– CodeHunter, Commented Sep 22, 2011 at 1:09

Joe · Accepted Answer · 2011-09-22 01:11:18Z

3

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.

answered Sep 22, 2011 at 1:11

Joe

42.9k20 gold badges108 silver badges129 bronze badges

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

1 Comment

CodeHunter Over a year ago

it works, some of the columns have value like 30.100000000000001 and it fails. Is there any workarounds?

Collectives™ on Stack Overflow

Arithmetic overflow error converting varchar to data type numeric?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related