Modulo difficulties, life is easy

The other day, I had a request from a BA. The gist of it was that we had a column in the database defined as a decimal(5,4) and yet the data as defined by the regulatory body only provided three places after the decimal point. He wanted to know if any data had somehow crept into the database that was utilizing the (seemingly superfluous) fourth place. On its face, this seems like a difficult problem. Admittedly, when I first heard it, I couldn't come up with something off the top of my head. But then I remembered that I have a plaque on my wall that says "B.S. Math", so I decided to leverage that. Enter the modulo operator.

Before we can talk about the modulo operator, we're going to have to flash back to when you learned long division. I know that it was traumatic for you, but trust me, this will bear fruit. If you recall, when you divide one number by another and that division doesn't result in "even division" (that is when the divisor goes into the dividend an integer number of times), you have the option of either expanding it into a decimal or just saying "screw it... here's what's left". By way of a worked example, if I divide 5 by 2, I can either say that it's 2.5, or 2 with a remainder of 1. In simplest terms, the modulo operator takes as arguments a divisor and a dividend and returns the remainder.

So how does that apply to the stated problem? That is, if I have an arbitrary number that's of the form 0.1234, how do I tell if there is a digit in the ten thousandths place? Here's what I did. If I multiply all such numbers by 10,000 and then evaluate that number modulo (mod for short) 10, that will return the ones place to me. If the result of that operation is not 0, then I have a non-zero digit in the ten thousandths place. For you code junkies out there:

select value
from table
where convert(int, 10000*value) % 10 > 0

The mod operator is useful in a lot of other situations. For instance, let's say that you need to break up a group into n smaller groups. Mod is your friend here. The following code will add a column to the result set called "g" that gives you the group number. In the example, I chose 2 as my modulus, so "g" will take values 0 and 1.

select row_number() over (order by Id) as [g] % 2, *
from table

Have you ever needed to make a report where alternating rows had different formatting (and your reporting tool of choice doesn't support this natively)? The above code makes this almost trivial.

So there you have it. The mod function can be a useful tool in your toolbox. Have fun!