SQLite Math Functions – Complete List with Examples

abs(), round(), random() and sign() are always available. The others are SQLite's math functions (3.35+), included in most builds but optional: check with SELECT ceil(1.5);. Remember that 7 / 2 is integer division in SQLite (3); write 7 / 2.0 for 3.5.

Name	Description	Since
`ABS()`	Return the absolute value of a number, so abs(-7) is 7. NULL gives NULL and text that isn't a number gives 0.0.
`ROUND()`	Round a number to a given number of decimal places (0 by default). Halves round away from zero.
`SIGN()`	Return -1, 0 or 1 depending on whether a number is negative, zero or positive. Non-numeric text gives NULL.
`RANDOM()`	Return a random 64-bit signed integer. Use abs(random()) % n or ORDER BY random() for random choices.
`CEIL()`	Return the smallest integer value not less than a number, rounding up: ceil(2.1) is 3. ceiling() is a synonym.	3.35.0
`FLOOR()`	Return the largest integer value not greater than a number, rounding down: floor(2.9) is 2 and floor(-2.1) is -3.	3.35.0
`TRUNC()`	Drop the fractional part of a number, rounding toward zero: trunc(2.7) is 2 and trunc(-2.7) is -2.	3.35.0
`MOD()`	Return the remainder of dividing one number by another. Unlike the % operator it also works with decimals.	3.35.0
`POWER()`	Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function.	3.35.0
`SQRT()`	Return the square root of a number, such as sqrt(16) = 4. Negative numbers give NULL rather than an error.	3.35.0
`EXP()`	Return e (about 2.718) raised to a power, so exp(1) is e. It is the inverse of the ln() function.	3.35.0
`LN()`	Return the natural logarithm (base e) of a number. Zero or negative numbers give NULL.	3.35.0
`LOG()`	Return the base-10 logarithm of a number, or the logarithm in any base with log(B, X). log10() and log2() also exist.	3.35.0
`PI()`	Return an approximation of pi (3.14159...). Useful for circles and converting between degrees and radians.	3.35.0

Name

Description

Since

ABS()

Return the absolute value of a number, so abs(-7) is 7. NULL gives NULL and text that isn't a number gives 0.0.

ROUND()

Round a number to a given number of decimal places (0 by default). Halves round away from zero.

SIGN()

Return -1, 0 or 1 depending on whether a number is negative, zero or positive. Non-numeric text gives NULL.

RANDOM()

Return a random 64-bit signed integer. Use abs(random()) % n or ORDER BY random() for random choices.

CEIL()

Return the smallest integer value not less than a number, rounding up: ceil(2.1) is 3. ceiling() is a synonym.

3.35.0

FLOOR()

Return the largest integer value not greater than a number, rounding down: floor(2.9) is 2 and floor(-2.1) is -3.

3.35.0

TRUNC()

Drop the fractional part of a number, rounding toward zero: trunc(2.7) is 2 and trunc(-2.7) is -2.

3.35.0

MOD()

Return the remainder of dividing one number by another. Unlike the % operator it also works with decimals.

3.35.0

POWER()

Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function.

3.35.0

SQRT()

Return the square root of a number, such as sqrt(16) = 4. Negative numbers give NULL rather than an error.

3.35.0

EXP()

Return e (about 2.718) raised to a power, so exp(1) is e. It is the inverse of the ln() function.

3.35.0

LN()

Return the natural logarithm (base e) of a number. Zero or negative numbers give NULL.

3.35.0

LOG()

Return the base-10 logarithm of a number, or the logarithm in any base with log(B, X). log10() and log2() also exist.

3.35.0

PI()

Return an approximation of pi (3.14159...). Useful for circles and converting between degrees and radians.

3.35.0