The SQL mod function is a mathematical function that returns the remainder of a division operation. This function takes two arguments: the dividend and the divisor. The dividend is the number to be divided, and the divisor is the number dividing the dividend. The return value of the MOD function is the remainder of the division operation.
It is commonly used in database programming to determine the next available primary key value, generate unique ID values, and determine if a number is odd or even. You can use the mod function in any programming language that supports SQL, i.e., MySQL, SQL Server, Oracle, and DB2.
SQL MOD function Syntax
The syntax for the SQL mod function is:
SELECT MOD(dividend, divisor);
The dividend is the number to be divided, and the divisor is the number to divide by.
Example 1
If we use the MOD function to divide 10 by 3, the result would be 1 because 10 divided by 3 equals 3 with a remainder of 1.
SELECT MOD(10,3);
Returns 1
Example 2
If we want to find the next available primary key value in a table with 10 records, we could use the following SQL statement:
SELECT MOD(MAX(id)+1, 10) FROM table_name;
Returns 1
It returns a value of 1, since the next available primary key value would be 11, i.e., (10 + 1).
generating Unique ID values using the SQL MOD function
If we want to generate a unique ID value for each record in a table, we could use the following SQL statement:
SELECT MOD(id, 10) FROM table_name;
Returns values between 0 and 9
It returns a different ID value for each record, between 0 and 9. You can then use these values to create unique URLs or file names.
The sql mod function can be very handy when working with large numbers of records. It is important to remember, however, that the results of the mod function are not guaranteed to be unique. If duplicate values are possible, another unique identifier should be used in conjunction with the mod function to ensure uniqueness.
SQL MOD function VS SQL DIV function
The SQL MOD function is often used in conjunction with the SQL DIV function. The DIV function returns the integer portion of a division operation, while the MOD function returns the remainder. For example, the following query would return both the integer and remainder values for each division operation:
select A, B, DIV(A,B), MOD(A,B) from TABLE_NAME;
| A | B | DIV(A,B) | MOD(A,B) |
| -------- | ---- | -------- | -------- |
| 10.33333 | 3.1 | 3 | .3333 |
| 20 | 5 | 4 | 0 |
| 100 | 7 | 14 | 2 |
Generating random numbers using SQL MOD function
The MOD function is often used in conjunction with the ROUND or TRUNC functions to create a range of numbers. For example, if you wanted to generate a list of random numbers between 1 and 10, you could use the following statement:
SELECT MOD(ROUND(RAND()*10),10)+1
Returns a random number between 1 and 10
ODD / EVEN number Using SQL MOD function
You can use the MOD function also to find out if a number is odd or even. It will return 0 for even numbers and 1 for odd numbers. Here is a good example.
The following statement would return all of the odd numbers in a table:
SELECT * FROM mytable WHERE MOD(mycolumn,2)=1
Conversely, the following statement would return all of the even numbers in a table:
SELECT * FROM mytable WHERE MOD(mycolumn,2)=0
Conclusion
We have looked at different ways you can use the SQL MOD function. For example, we have seen how to use it to get odd and even numbers, generate random numbers and create unique IDs.
If you enjoy our content, please consider buying us a coffee to support our work:
