Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

Back to Library

MOD() in SQL

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Syntax

MOD(dividend, divisor) or dividend % divisor

Return type

INTEGER/NUMERIC (matches input type)

MOD() Function Example

SELECT MOD(17, 5); 
-- Output: 2 (remainder when 17 is divided by 5)

What is MOD() in SQL?

The MOD() function in SQL returns the remainder after dividing one number by another. It is commonly used for checking divisibility, cyclic operations, and mathematical computations. Many databases also support the % operator as an alternative to MOD(), though syntax may vary. This function is available in MySQL, PostgreSQL, SQL Server, and Oracle. If the divisor is zero, the function may return an error or NULL, depending on the database system. MOD() is useful in scenarios such as identifying even/odd numbers, distributing records into groups, and implementing circular indexing.

Parameters:

dividend: The number to be divided
divisor: The number to divide by

Example Use Cases:

-- Check for even/odd numbers

SELECT CASE WHEN MOD(number, 2) = 0 THEN 'Even' ELSE 'Odd' END FROM numbers; 
-- Output:
parity
------
Even
Odd
Even
Odd
Even

-- Rotate through values (like days of week)

SELECT MOD(day_number, 7) AS weekday FROM dates; 
-- Output:
weekday
-------
0
1
2
3
4

-- Distribute items into groups

SELECT MOD(item_id, bucket_count) AS bucket_number FROM items;
-- Output:
bucket_number
------------
2
0
1
3
2

Notes:

Behavior: Returns NULL if divisor is 0
Performance Considerations: Basic arithmetic operation, efficient
Version Info: Core mathematical function available in all major DBMS
Deprecated/Recommended Alternatives: % operator often preferred

Error Handling:

Error: Returns NULL if either input is NULL or if divisor is 0
Recommendation: Use NULLIF to handle zero divisor cases

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

MOD() or %

MOD(17, 5)

Returns NULL

PostgreSQL

MOD() or %

MOD(17, 5)

Returns NULL

SQL Server

% operator

17 % 5

Returns NULL

SQLite

% operator

17 % 5

Returns NULL

BigQuery

MOD()

MOD(17, 5)

Returns NULL

Snowflake

MOD() or %

MOD(17, 5)

Returns NULL

Athena

MOD()

MOD(17, 5)

Returns NULL

Related Functions:

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

ROUND()

Rounds a number to a specified number of decimal places or to the nearest integer.

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

Get Sherloq Free

MOD() in SQL

Syntax

Return type

MOD() Function Example

What is MOD() in SQL?

Parameters:

Example Use Cases:

Notes:

Error Handling:

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

PostgreSQL

SQL Server

SQLite

BigQuery

Snowflake

Athena

Related Functions:

FLOOR()

CEIL()/CEILING()

ROUND()

SIGN()

ABS()

RAND()/RANDOM()

MOD()

SQRT()

POWER()

One place for all your queries, directly on your SQL editor

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

One place for all your queries,
directly on your SQL editor