SQL MOD and REMAINDER Function Guide, FAQ, and Examples

The SQL 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 SQL 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.

SQL MOD Function (Oracle, MySQL, Postgres)

The SQL 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.

It exists in Oracle, MySQL, and Postgres. In SQL Server, you can use the % operator.

 

SQL MOD Syntax

The SQL 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)

 

SQL REMAINDER Syntax and Parameters

The syntax of Oracle REMAINDER is:

REMAINDER ( n2, n1 )

The parameters of the 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.

 

What’s the Difference between Oracle MOD and REMAINDER?

They are both pretty similar. The difference is that REMAINDER uses ROUND in its calculation, and MOD uses the FLOOR function.

ROUND can go up or down, but FLOOR always goes down.

The main differences are when you use negative numbers.

 

Is There an 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 SQL 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 SQL MOD Function In a WHERE Clause?

Yes, you can use the SQL 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.

 

Example 1

This example should show a positive result.

SELECT REMAINDER(24, 7);

Result:

3

The result is 3, because 7*3 is 21, and there is 3 left over.

 

Example 2

This example demonstrates a remainder of 0.

SELECT REMAINDER(24, 6);

Result:

0

The result is 0, because 6 goes into 24 exactly 4 times, with no remainder.

 

Example 3

This example demonstrates a negative remainder.

SELECT REMAINDER(24, 5);

Result:

-1

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.

 

Example 4

This is an example using larger numbers.

SELECT REMAINDER(627, 101) AS REMTEST
FROM dual;

Result:

21

The result is 21, because 101*6 is 606, and 627-606 is 21.

 

Example 5

This example uses two negative numbers as inputs.

SELECT REMAINDER(-58, -10);

Result:

2

The result is 2, because -10*6 = -60, and there is a remainder of 2.

 

Examples of the MOD Function

Here are some examples of the MOD function.

Example 1

This example is a basic use of the MOD function.

SELECT MOD(15, 4);

Result:

3

The result is 3 because there is 3 left over after you perform 15/4.

 

Example 2

This example uses a decimal as the first parameter.

SELECT MOD(15.5, 4);

Result:

3.5

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.

 

Example 3

This example uses decimals for both parameters.

SELECT MOD(15.5, 4.5);

Result:

2

The result is 2, because 4.5 goes into 15.5 twice.

 

Example 4

This example uses 0 as the second parameter.

SELECT MOD(15, 0);

Result:

15

The result is 15, because the MOD function returns the first parameter if the second parameter is 0.

 

Example 5

This example uses a number stored as a character value.

SELECT MOD(15, '6');

Result:

3

The result is 3. The function performs the same way, even though the number is stored as a char type.

 

Example 6

This example is another basic example with different numbers.

SELECT MOD(22, 8);

Result:

6

The result is 6, as that is what is left over after you perform 22/8.

 

Example 7

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;

Result:

FIRST_NAME	LAST_NAME	RN
John	Smith	1
Susan	Johnson	2
Tom	Capper	3
Mark	Holloway	4
Steven	Webber	5
Julie	Armstrong	6
Michelle	Randall	7
Andrew	Cooper	8
Robert	Pickering	9
Tanya	Hall)	10

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;

Result:

FIRST_NAME	LAST_NAME	RN
Susan	Johnson	2
Mark	Holloway	4
Julie	Armstrong	6
Andrew	Cooper	8
Tanya	Hall)	10

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!