Uses of the modulo operator: How Oracle mod can make your life easier.
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”
|
1 2 3 |
SELECT MOD(6,3) FROM DUAL; 6-3=3 3-3=0 (remainder is 0) |
|
1 2 |
SELECT MOD(3,6) FROM DUAL; 3-6=-3 (result is negative so remainder is 3) |
|
1 2 3 |
SELECT MOD(5,3) FROM DUAL; 5-3=2 2-3 (result is negative so remainder is 2) |
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.
|
1 |
SELECT FLOOR(10000/3600) || ':' || FLOOR(MOD(10000/60,60)) || ':' || MOD(10000,60) FROM DUAL; |
Use case 2: Finding even or odd numbers
|
1 2 3 |
SELECT 10,CASE WHEN mod(10,2) = 0 THEN 'even' else 'odd' END FROM DUAL UNION SELECT 9,CASE WHEN mod(9,2) = 0 THEN 'even' else 'odd' END FROM DUAL; |
Use case 3: Expressing something in decimal form
|
1 |
SELECT FLOOR(7/5) || ' + ' || MOD(7,5) || '/5' FROM DUAL; |
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.
|
1 |
SELECT MOD(object_id,2), x.* FROM all_objects x WHERE MOD(object_id,2) = 1 order by object_id; |
Selecting 2/3 of records
|
1 |
SELECT MOD(object_id,3), x.* FROM all_objects x WHERE MOD(object_id,3) in (1,2) ORDER BY object_id; |
Use case 5: Get last M digits from a number
Get the last digit:
|
1 |
SELECT MOD(98,10) FROM DUAL; |
Get the last two digits:
|
1 |
SELECT MOD(980,100) FROM DUAL; |
and so on.
Let me know how you use the mod operator to make your life easier.
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
