Home » Oracle SQL » Oracle MOD

Oracle MOD

MOD is an Oracle function which returns the remainder after dividing one value with other.

Syntax

The syntax of Oracle function is as follows:

MOD( x, y)

Here,

x – The value that MOD function will divide

y – The value that is used for dividing

MOD function divides x with y and returns the remainder.

MOD Function Examples

1. MOD with numeric values

Lets see a query used for finding MOD by giving an input of two values:

SELECT MOD(27,5) FROM dual;

Result:

MOD with Numeric Value in Oracle SQL

The query returns the MOD of 27 divided by 5 which is 2.

2. MOD with Columns

Lets see an example where we use MOD function to find salary of employees in words:

SELECT first_name, last_name, TRUNC(SALARY/1000, 0) || ' thousands ' ||
TRUNC( MOD(SALARY,1000)/100, 0) || ' hundreds ' SALARY
FROM employees ;

Result:

MOD with Column in Oracle SQL

The query returns Name of employees and their salary in words using MOD function.

This query works in 3 steps:

  1. First, we calculate salary divided by thousand and then we truncate the value by giving n=0, which means no decimal places. So, a salary of 24400 returns only 24 with no decimal places. Then, we concatenate string ‘thousands’ to it.
  2. Then, we find the MOD of salary divided by 1000 and then we divide that value by 100. So for example, if the salary is 4800. Then MOD(SALARY,1000), 0) will return value 800. But, if use MOD(SALARY,1000/100), 0) then it returns 8 and we concatenate string ‘hundreds’ to it.
  3. By concatenating all results, we get the final result.

In this tutorial, we learned how to use Oracle MOD function to find mathematical mod of values.