Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT 17 % 5 AS remainder;   -- 2

Try it

SQL Remainders, signs and zero

Press Run (or Ctrl + Enter) to see the result here.

SQL Even and odd order ids ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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, so 7.5 % 2 is 1, not 1.5. PostgreSQL, MySQL and Oracle keep the fraction with MOD(7.5, 2).
  • A true mathematical modulo (never negative) needs ((a % b) + b) % b when a can be negative and b positive.
  • Even/odd: n % 2 = 0 is even. For negative n, n % 2 can be -1, so test n % 2 <> 0 for odd.
  • Round-robin assignment: id % 3 gives 0, 1, 2, 0, 1, 2... to spread rows over three groups.
  • Filtering on id % n cannot use an index on id. Use it for sampling and reports, not for hot lookups.
  • Pairs with integer division: for non-negative values, a = (a / b) * b + a % b when both are integers.
  • Division by zero is a silent NULL in SQLite and MySQL but an error elsewhere. Guard with NULLIF(b, 0).