MySQL - MOD() Function

The MySQL MOD() function is used to perform the modulus operation on two numeric values.

Modulus operation is same as performing an ordinary division operation on two numbers, but retrieving the remainder as a result instead of the quotient. It is represented by the symbol '%'.

This MySQL function accepts two integer values say X and Y (in the same order) and, returns the remainder of the division operation X divided by Y.

Syntax

Following is the syntax of this function −

MOD(X,Y);

Parameters

This function takes two integer values as a parameter.

Return Value

This function returns the remainder of the division operation.

Another Syntax

Following is the another syntax of mod() function in MySQL −

X % Y;
Or,
X MOD Y;

Example

The following query uses MySQL MOD() function to calculate the remainder when 229 is divided by 5 −

SELECT MOD(229, 5) As Result;

Output

The output for the query above is produced as given below −

Result
4

Example

We can also pass parameters to this function as string values −

SELECT MOD('555229', '25') As Result;

Output

This will produce the following result −

Result
4

Example

We can also use the symbol % instead of this MOD() function −

SELECT 25643 % 50 As Result;

Output

Following is the output −

Result
43

Example

The expression N MOD M also performs the same functionality −

SELECT 37 MOD 7 As Result;

Output

The output for the query above is produced as given below −

Result
2

Example

This function also accepts values with decimals as parameters −

SELECT MOD(57785.658778, 557.36) As Result;

Output

The output is displayed as below −

Result
377.578778

Example

If you pass 0 as the value for the parameter Y this function returns NULL −

SELECT MOD(45673, 0) As Result;

Output

The output is displayed as below −

Result
NULL

Example

If you pass 0 as the value for the parameter X this function returns 0 always −

SELECT MOD(0, 5) As Result;

Output

The output for the query above is produced as given below −

Result
0

Example

In the following example, we are creating a MySQL table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the following query to display all the records present in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are using the MOD() function to calculate the remainder when the SALARY is divided by 2. If the remainder is 0, it means the 'SALARY' is even; else, it's odd.

SELECT ID, NAME, ADDRESS, SALARY,
IF(MOD(SALARY, 2), 'Odd', 'Even') as OddOrEven FROM CUSTOMERS;

Output

The output for the query above is produced as given below −

ID NAME ADDRESS SALARY OddOrEven
1 Ramesh Ahmedabad 2000.00 Even
2 Khilan Delhi 1500.00 Even
3 Kaushik Kota 2000.00 Even
4 Chaitali Mumbai 6500.00 Even
5 Hardik Bhopal 8500.00 Even
6 Komal Hyderabad 4500.00 Even
7 Muffy Indore 10000.00 Even
Advertisements