SQL Reference ยท Scalar Functions
SQL MOD() function and the % operator
Reference for SQL MOD() and the % operator: the remainder of a division, sign rules, division by zero, even and odd checks and dialect differences.
MOD(a, b) returns the remainder left over when a is divided by b. Many databases also spell it with the % operator: a % b. It is useful for even/odd tests, cycling through a fixed number of groups, wrapping values (hours on a clock) and splitting rows into buckets.
In SQLite, the portable spelling is the % operator, because the mod() function exists only in builds with the math functions option and not in the one this site runs.
SELECT 17 % 5 AS remainder; -- 2
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
MOD(dividend, divisor) dividend % divisor
| Name | Description |
|---|---|
dividend |
The number being divided. |
divisor |
The number to divide by. |
Returns: the remainder. Its sign follows the dividend in SQLite, PostgreSQL, MySQL, SQL Server and Oracle's MOD. So -17 % 5 is -2, not 3. NULL if either argument is NULL.
Dialect differences
| Database | Syntax | Division by zero |
|---|---|---|
| Standard SQL | MOD(a, b) |
error |
| SQLite | a % b; mod(a, b) in builds with math functions |
NULL |
| PostgreSQL | MOD(a, b) and a % b |
error |
| MySQL | MOD(a, b), a % b, a MOD b |
NULL |
| SQL Server | a % b only (no MOD function) |
error |
| Oracle | MOD(a, b) only (no % operator) |
returns a |
-- SQL Server SELECT 17 % 5; -- Oracle SELECT MOD(17, 5) FROM dual;
Usage notes
- SQLite
%works on integers. Real operands are converted to integers first, so7.5 % 2is1, not1.5. PostgreSQL, MySQL and Oracle keep the fraction withMOD(7.5, 2). - A true mathematical modulo (never negative) needs
((a % b) + b) % bwhenacan be negative andbpositive. - Even/odd:
n % 2 = 0is even. For negativen,n % 2can be-1, so testn % 2 <> 0for odd. - Round-robin assignment:
id % 3gives 0, 1, 2, 0, 1, 2... to spread rows over three groups. - Filtering on
id % ncannot use an index onid. Use it for sampling and reports, not for hot lookups. - Pairs with integer division: for non-negative values,
a = (a / b) * b + a % bwhen both are integers. - Division by zero is a silent
NULLin SQLite and MySQL but an error elsewhere. Guard withNULLIF(b, 0).