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!
Post a Comment