Division by zero!

[DavidLemire]

Hi people,

I need a little guidance here. To make a long story short, I have a string which would be "(123/0)+23". I need to put that string in a dynamic query. However, I want to avoid the division by zero error.

if charindex('/0',@DynamicNumber) <> 0

that won't work here, because I can have 0.002...

Is there a way to do the update query: update #output set Column = @DynamicNumber

and catch the error if I get a division by zero. In that case, I would just like to let the cell as a null value.

Thanks for your help

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[JamesLean]

Try this:

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON

DECLARE @t table (a int, b int)

INSERT @t VALUES (10, 999)
INSERT @t VALUES (0, 999)
INSERT @t VALUES (20, 999)

UPDATE @t
SET b = 100 / a

SELECT * FROM @t

--James

 

[EdwinGene]

In the first place, charindex() will not work, because charindex() looks for only one char. You will have to use patindex(), which looks for a string.

Secondly, could you use patindex() to look for '/0)'?

 

[JamesLean]

charindex() will not work, because charindex() looks for only one char

Er, not true. CHARINDEX searches for a fixed string of character(s). PATINDEX searches for a pattern.

SELECT CHARINDEX('now', 'how now brown cow'),
	PATINDEX('_ow%', 'how now brown cow')

--James

 

[DavidLemire]

Thanks JamesLean!!

I tried with ArithIgnore, and it didn't work. Didn't know I had to put off ArithAbort and Ansi_warnings

I love you guys, it works perfectly now!

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[EdwinGene]

You're right, James. Sorry. I remembered wrong.