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

Posted by on Oct 11, 2012 in Oracle

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”

SELECT MOD(6,3) FROM DUAL;
 6-3=3
 3-3=0 (remainder is 0)
SELECT MOD(3,6) FROM DUAL;
 3-6=-3 (result is negative so remainder is 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.

SELECT FLOOR(10000/3600) || ':' || FLOOR(MOD(10000/60,60)) || ':' || MOD(10000,60)  FROM DUAL;

 Use case 2: Finding even or odd numbers

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

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.

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

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:

SELECT MOD(98,10) FROM DUAL;

Get the last two digits:

SELECT MOD(980,100) FROM DUAL;

and so on.

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

 

Most Popular Posts

Leave Reply