Geeks With Blogs
John Workman The Workdog's weblog.... a brain dump, of sorts.

Someone asked me a very simple question, and I was so surprised at how difficult it became to answer.  They wanted to know how to tell if a number is evenly dividable by another number.  (e.g. If you have a case of 24, you can sell it in 6 packs, but you can’t sell it in 7 packs.)  I quickly said to use the MOD function and check for a zero return value.  I walked away with confidence that this issue was resolved.  I’ve done this before, I know it works....somewhere. 

 

The problem is, that in SQLServer2000, you can only use modulo (%) with integers.  If you need to allow for decimals, it will not work.  This person is very resourceful.  When she discovered this, instead of running back to say it didn’t work, she kept digging until she found another way.  If the ceiling() and the floor() are equal, then they must be evenly dividable.  This appears to work well, but I can’t seem to stop questioning it.  Is this really the best way?  Are we going to take a performance hit from calling two functions for every row?  This seems like such a simple thing, part of me feels like it’s a little over engineered.  That being said, I also like it because I think it’s clever. 

 

Obviously our sql is more complex than this, but it’s a good example. 

 

declare @numOne decimal (8,6), @numTwo decimal (8,6)

select @numOne = 24, @numTwo = 6.2

 

select cast(@numOne as int) % cast(@numTwo as int)

 

select case when (ceiling(@numOne/@numTwo) = floor(@numOne/@numTwo)) then 0 else 1 end as Even

 

If you’ve run into this issue before, or have any thoughts concerning it, I’d be interested to read your comments.

Cheers,
John

 

**** NEW INFO ***

Thanks to Feedback from Nuri, I found out that SQL Server 2005 supports modulo for numeric data types.  Here's the details from SQL Server 2005 help:

Syntax

 

dividend % divisor

Arguments

dividend

Is the numeric expression to divide. dividend must be a valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type.

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type.

Posted on Thursday, August 10, 2006 8:17 PM | Back to top


Comments on this post: SQLServer Modulo (%) with decimals instead of integers

# re: SQLServer Modulo (%) with decimals instead of integers
Requesting Gravatar...
just ran this:

declare @numOne decimal (8,6), @numTwo decimal (8,6)
select @numOne = 24, @numTwo = 6.2
select @numOne, @numTwo, @numOne % @numTwo

--and got
24.000000 6.200000 5.400000

This is on SQL Server Express 2005.
Upgrade from 2000 to 2005?

The issue with MOD on number with fractions is that expressions such a 0.3333333 do not yield "clean" remainders anyway. So one needs to be careful about any comparison to 0 type algorithm.
Left by Nuri on Aug 10, 2006 10:57 PM

# re: SQLServer Modulo (%) with decimals instead of integers
Requesting Gravatar...
This is good to know. Thanks! We are upgrading to 2005 by the end of the year. It's in labs now. This is actually going to be used in the where clause to only return rows that evenly divide. I’ll edit my post so other easily see that it’s there in 2005.
Left by John Workman on Aug 11, 2006 5:30 AM

Your comment:
 (will show your gravatar)


Copyright © John Workman | Powered by: GeeksWithBlogs.net