select mod(1, 3) from dual
he MOD or modula function is used to return the remainder from a division. If there is no remainder, the number zero is returned. This function can be used to control a loop that is only to process every nth record.
For example, a sampling algorithm for marketing may only want to look at every hundredth record out of a base of a million records to get a representative sample and not have to deal with the full set of records.
Syntax
>ÄÄMODÄÄ(m,n)ÄÄÄ><
PL/SQL Example
You can use MOD to determine quickly if a number is odd or even:
FUNCTION is_odd (num_in IN NUMBER) RETURN BOOLEAN
IS
BEGIN
RETURN MOD (num_in, 2) = 1;
END;
FUNCTION is_even (num_in IN NUMBER) RETURN BOOLEAN
IS
BEGIN
RETURN MOD (num_in, 2) = 0;
END;
SQL Example
SELECT MOD(11,4) "Modulus"
FROM DUAL
Modulus
----------
3
This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)
The following statement illustrates the difference between the MOD function and the classical modulus:
SELECT m, n, MOD(m, n),
m - n * FLOOR(m/n) "Classical Modulus"
FROM test_mod_table
M N MOD (M,N) Classical Modulus
--- ---- -------- --------- -------
11 4 3
-11 4 -3 1
11 -4 3 -1
-11 -4 -3 -3