SQL,

Uses of the modulo operator: How Oracle mod can make your life easier.

Published on October 11, 2012
Updated on September 17, 2024

You may ask, what the heck is modulo? Well, below is an easy to understand definition.

“Modulo basically means keep taking the second number away from the first number. When you can’t do it any more without going into negative numbers, whatever’s left is the answer”

Ok. That is great. But how can we actually benefit from this.
What are common use cases?

Use case 1: Wrap values such as in a clock, e.g. convert seconds to hours, minutes, seconds.

 Use case 2: Finding even or odd numbers

Use case 3: Expressing something in decimal form

Use case 4: Distribute a dataset into buckets in a round robin fashion

An example would be to update only every second or third record in a table that contains a sequenced list of items. If you don’t have a sequence you can use rownum.

Selecting 2/3 of records

Use case 5: Get last M digits from a number

Get the last digit:

Get the last two digits:

and so on.
Let me know how you use the mod operator to make your life easier.