The Oracle MOD and REMAINDER functions are very similar and are useful for working with numbers and even for other operations. Learn what they are, and the differences, in this article.
Purpose of the Oracle REMAINDER and MOD Functions
Oracle REMAINDER Function
The Oracle REMAINDER function finds the remainder of one number divided by another number. So, if you have two numbers (n1 and n2), you take n2, divide it by n1, and whatever is left over is the result of this function.
Oracle MOD Function
The Oracle MOD function performs a “modulo division”, which means that it gets the remainder of one number divided by another number. It returns the leftover of a division of two numbers.
What’s the difference?
What’s the Difference between Oracle MOD and REMAINDER?
ROUND can go up or down, but FLOOR always goes down.
The main differences are when you use negative numbers.
Oracle REMAINDER Syntax and Parameters
The syntax of Oracle REMAINDER is:
REMAINDER ( n2, n1 )
The parameters of the Oracle REMAINDER function are:
- n2 (mandatory): This is the number that is divided into. If you think of it as a fraction, this is the number on top, or the numerator.
- n1 (mandatory): This is the number that is used to divide into the other number. If you think of it as a fraction, this is the number on the bottom, or the denominator.
Some other points to note about this function:
- The n2 and n1 inputs can be any numeric data type.
- The return type is a numeric data type, depending on the input data types.
- If n1=0, or n2=infinity, then Oracle will return an error if the input types are NUMBER, or NaN if the input types are BINARY_FLOAT or BINARY_DOUBLE.
- If n1 is not 0, then the remainder is n2 – (n1*N), where N is the integer nearest to n2/n1.
- If n2 is a floating point number and the remainder is 0, then the sign of the remainder is the sign of n2.
So, just to highlight, the calculation for REMAINDER is:
n2 – (n1*N), where N is the integer nearest to n2/n1.
Oracle MOD Syntax
The Oracle MOD syntax is below:
MOD ( numerator, denominator )
The return type is a numeric data type, and the exact type depends on the input parameters.
The parameters of the MOD function are:
- numerator (mandatory): This is the numerator in the division, or the “number on the top”. In a division such as 15/4, this would be 15. It is most likely the higher of the two numbers.
- denominator (mandatory): This is the denominator in the division, or the “number on the bottom”. In a division such as 15/4, this would be 4. It is most likely the smaller of the two numbers.
Some things to note about this function:
- The data types for both numerator and denominator can be any numeric data type, or anything that can be converted into a numeric data type.
- The MOD function will return the value of the numerator if the denominator is 0, to prevent “divide by zero” errors.
- The MOD function uses the FLOOR function in its formula.
- The calculation for MOD is: numerator – denominator * FLOOR(numerator/denominator)
Is There A Oracle MOD DIV Function?
No, there is no DIV function in Oracle.
You can use the / symbol to get the division result, and possibly include it in a TRUNC or FLOOR so that it removes the decimals.
SELECT TRUNC(18/5) FROM dual;
How Can You Select Every Second Row Using Oracle MOD and Rownum?
Do you need to be able to see every 2nd row in a result set?
You can do this using MOD.
First, select the data from your table that you need, along with the rownum. Don’t filter on the rownum yet.
Then, contain this within an outer query. Filter the outer query on the rownum using MOD.
This is because ROWNUM returns the row number of the actual query, not the table it is loaded from.
An example would be:
SELECT first_name, last_name, rn ( SELECT first_name, last_name, rownum as rn FROM customers ) c WHERE MOD(rn, 2) = 0;
You can change the value of “2” inside MOD to get a different interval (e.g. 5 for every 5th record).
Can You Use Oracle MOD Function In a WHERE Clause?
Yes, you can use the Oracle MOD function in a WHERE clause. It works similar to other functions. Just remember, though, to get your parameters correct.
Examples of the REMAINDER Function
Here are some examples of the REMAINDER function. I find that examples are the best way for me to learn about code, even with the explanation above.
This example should show a positive result.
SELECT REMAINDER(24, 7) AS REMTEST FROM dual;
The result is 3, because 7*3 is 21, and there is 3 left over.
This example demonstrates a remainder of 0.
SELECT REMAINDER(24, 6) AS REMTEST FROM dual;
The result is 0, because 6 goes into 24 exactly 4 times, with no remainder.
This example demonstrates a negative remainder.
SELECT REMAINDER(24, 5) AS REMTEST FROM dual;
This result is -1, because 5*5 is closer to 24 than 5*4 (25 vs 20), so 25 is used for the calculation. 24-25 is -1.
This is an example using larger numbers.
SELECT REMAINDER(627, 101) AS REMTEST FROM dual;
The result is 21, because 101*6 is 606, and 627-606 is 21.
This example uses two negative numbers as inputs.
SELECT REMAINDER(-58, -10) AS REMTEST FROM dual;
The result is 2, because -10*6 = -60, and there is a remainder of 2.
This example is a basic use of the MOD function.
SELECT MOD(15, 4) AS MODTEST FROM dual;
The result is 3 because there is 3 left over after you perform 15/4.
This example uses a decimal as the first parameter.
SELECT MOD(15.5, 4) AS MODTEST FROM dual;
The result is 3.5, which works in a similar way to Example 1. There is 3.5 left over after the 15.5/4 operation.
This example uses decimals for both parameters.
SELECT MOD(15.5, 4.5) AS MODTEST FROM dual;
The result is 2, because 4.5 goes into 15.5 twice.
This example uses 0 as the second parameter.
SELECT MOD(15, 0) AS MODTEST FROM dual;
The result is 15, because the MOD function returns the first parameter if the second parameter is 0.
This example uses a number stored as a character value.
SELECT MOD(15, '6') AS MODTEST FROM dual;
The result is 3. The function performs the same way, even though the number is stored as a char type.
This example is another basic example with different numbers.
SELECT MOD(22, 8) AS MODTEST FROM dual;
The result is 6, as that it what is left over after you perform 22/8.
This example shows how you can get every second row in a table using MOD.
First, let’s see the whole table.
SELECT first_name, last_name, rownum as rn FROM student;
Now, let’s see every second row.
SELECT first_name, last_name, rn FROM ( SELECT first_name, last_name, rownum as rn FROM student ) c WHERE MOD(rn, 2) = 0;
This shows every second row in the table.
You can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!