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:
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:
The query returns Name of employees and their salary in words using MOD function.
This query works in 3 steps:
- 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.
- 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.
- 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.