Fixing Divide by Zero Error

Question

Fixing Divide by Zero Error

VegasL

Hall of Fame

Points: 3006
More actions
April 15, 2013 at 10:52 pm

#273037

Hello,
I have in select statement select t1.A/t1.B but get divide by zero error
when I used with isnull isnull(t1.A/t1.B,'-')
I get error Error converting data type varchar to float.
how to fix?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

VegasL (4/15/2013)
Hello,
I have in select statement select t1.A/t1.B but get divide by zero error
when I used with isnull isnull(t1.A/t1.B,'-')
I get error Error converting data type varchar to float.
how to fix?
Thanks

select t1.A / nullif(t1.B,0)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

VegasL (4/15/2013)
Hello,
I have in select statement select t1.A/t1.B but get divide by zero error
when I used with isnull isnull(t1.A/t1.B,'-')
I get error Error converting data type varchar to float.
how to fix?
Thanks

You can handle this using CASE also:

SELECT CASE when t1.b =0 THEN 0 ELSE t1.A/t1.B

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Johan Bijnens SSC Guru Points: 135431 More actions · Answer 3

why are you storing numeric values in a varchar column. As experienced, this will get you in troubles.

( in most cases because of formatting issues or just because someone put non-numeric data in it)

There must be a zero ( 0 ) in your t1.B column.

add a case statement to check it so it doesn't produce the error.

select t1.a / case t1.b when 0 then NULL /* to the result will be null */

else t1.B

end as TheResult

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

VegasL Hall of Fame Points: 3006 More actions · Answer 4

Thanks Lynn, you'res was the easiest solution for me. I'll have to look into the others more. Thanks All!

John_Hill Ten Centuries Points: 1173 More actions · Answer 5

I would agree with Lynn's post. You can not divide by zero, but if you divide by NULL, you'll get NULL.

DECLARE @i FLOAT = 10, @x FLOAT = 0

SELECT @i / NULLIF(@x,0)

Fixing Divide by Zero Error

Cookies on SQLServerCentral