SQLite mod() Function

Summary: in this tutorial, you will learn how to use the SQLite mod() function to return the remainder after dividing the first argument by the second one.

Introduction to the SQLite mod() function

In SQLite, the mod() function returns the remainder after dividing the first argument by the second one.

Here’s the syntax of the mod() function:

mod(x,y)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• x is the dividend.
• y is the divisor.

The mod() function returns the remainder when x is divided by y. If x and/or y is NULL, the mod() function returns NULL.

The mod() function works like the % operator except that it also works with the non-integers.

SQLite mod() function examples

Let’s explore some examples of using the mod() function.

1) Basic SQLite mod() function example

The following example uses the mod() function to get the remainder of 7 and 4:

SELECT mod(7,4) remainder;Code language: SQL (Structured Query Language) (sql)

Output:

remainder
---------
3.0Code language: SQL (Structured Query Language) (sql)

As mentioned earlier, the mod() function also works with non-integers:

SELECT mod(11.0, 2.0) remainder;Code language: SQL (Structured Query Language) (sql)

Output:

remainder
---------
1.0Code language: SQL (Structured Query Language) (sql)

The following example uses the mod() function with a negative dividend:

SELECT mod(-7,2) remainder;Code language: SQL (Structured Query Language) (sql)

Output:

remainder
---------
-1.0Code language: SQL (Structured Query Language) (sql)

The following statement uses the mod() function for both negative dividend and divisor:

SELECT mod(-7,-2) remainder;Code language: SQL (Structured Query Language) (sql)

Output:

remainder
---------
-1.0Code language: SQL (Structured Query Language) (sql)

2) Using the mod() function to calculating the leap year

The following example uses the mod() function to check if 2024 is a leap year or not:

SELECT mod(2024, 4) = 0 and (mod(2024, 100) <> 0 or mod(2024, 400) = 0) is_leap_year;Code language: SQL (Structured Query Language) (sql)

Output:

is_leap_year
------------
1Code language: SQL (Structured Query Language) (sql)

The output indicates that 2024 is the leap year. If it is not a leap year, the query will return 0 instead.

Please note that a year is a leap year if it is divisible by 4, but not by 100 unless it is also divisible by 400.

Summary

• Use the mod() function to get the remainder after dividing a number by another.