Welcome to our blog, where we're demystifying SQL functions. Today, let's dive into the MOD() function. It's like a helpful tool in SQL that deals with remainders, making math in databases easier.

MOD() is a function in Structured Query Language (SQL) that can be used to find the remainder when a number is divided by another number. MOD() function in SQL takes two arguments both of numeric data type, the first argument is the dividend, and the second argument is the divisor. If the divisor provided is zero, then it gives NULL as output.

It follows the following algebraic expression to carry out the calculation.

M -N*(floor(M/N))

Syntax of MOD() Function in SQL

These are the syntax that can carry out the MOD() function in SQL.

MOD(P, Q)

Or

P%Q

Or

P mod Q

‘%’ is used to carry out the function of MOD() function in databases which don’t support the MOD() function.

Parameter of MOD() Function in SQL

In SQL, the MOD() function is used to find the remainder of a division operation. Its parameters include the dividend and the divisor. Here's a breakdown:

Dividend: This is the numeric value you want to find the remainder for. It's the number you're dividing.

Divisor: The divisor is the numeric value you're dividing by. It represents the number of equal parts you're dividing the dividend into.

Examples of MOD function

To see the working and use of the MOD() function in SQL, let’s look at some examples.

Example 1:

It can be used to find the remainder when one number is divided by another number.

Code

Select MOD(22,4)

Output

MOD(22,4)
2

Example 2:

It can also be used to find the remainder of a floating point number.

Code

select MOD(11.35, 3)

Output

MOD(11.35, 3)
2.35

Example 3:

MOD() function in SQL can be used in queries with clauses such as WHERE, SELECT, IF, etc.

Let’s Take an example where we create a Table Student and insert Data into the table. We can use the MOD() function to find out the students who have even roll numbers.

Table Creation

CREATE TABLE Student
{
rollno int NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
PRIMARY KEY(rollno)
};

MOD() in SQL returns the remainder of a division operation.

When to use MOD in SQL?

You can use MOD in SQL when you need to find the remainder in a division operation, like checking for even or odd numbers.

What is the alternative of MOD in SQL?

The alternative to MOD in SQL is the '%' symbol, which serves as the modulo operator.

What is the mod operator in SQL Plus?

In SQL Plus, the mod operator is '%', used for calculating remainders in arithmetic operations.

Conclusion

In this article, we learned the use of the MOD() function in SQL and its Different Syntaxes. The MOD() function in SQL proves invaluable for tasks involving remainders in division operations. Whether identifying even or odd numbers or implementing intricate calculations, MOD() enhances precision and efficiency.