SQL Dialects Reference/Functions and expressions/Math functions/Numeric functions

Numeric functions

edit

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Absolute value of x ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x) ABS(x)
? Sign of number x N/A SIGN(x) SIGN(x) SING(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) SIGN(x) N/A SIGN(x)
? Modulus (remainder) of   MOD(x, y) MOD(x, y) MOD(x, y) MOD(x, y) MOD(x, y) x % y x % y
MOD(x, y)
x % y
MOD(x, y)
MOD(x, y) x % y
MOD(x, y)
x % y MOD(x, y)
? Smallest integer >= x CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEIL(x)
CEILING(x)
CEIL(x) CEILING(x) CEILING(x)
CEIL(x)
CEILING(x)
CEIL(x)
CEIL(x) CEILING(x)
CEIL(x)
N/A CEILING(x)
? Largest integer <= x FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) FLOOR(x) N/A FLOOR(x)
? Round x (to precision of d digits) N/A ROUND(x, d) ROUND(x, d) ROUND(x, d) ROUND(x[, d]) ROUND(x[, d]) ROUND(x[, d]) ROUND(x, d) ROUND(x[, d]) ROUND(x[, d]) ROUND(x[, d]) ROUND(x)
? Truncate x to n decimal places N/A TRUNCATE(x, n)
TRUNC(x, n)
TRUNC(x[, n]) TRUNCATE(x, n)
TRUNC(x, n)
TRUNC(x[, d]) ROUND(x[, d], 1) TRUNCATE(x[, dn) sys.ms_trunc(x, n) TRUNC TRUNC(x[, y]) N/A N/A
? Square root of x ( ) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) SQRT(x) N/A SQRT(x)
? Exponent of x ( ) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) N/A EXP(x)
? Power ( ) POWER(x, y) POWER(x, y) POWER(x, y) POWER(x, y)
x ** y
POWER(x, y) POWER(x, y) POW(x, y)
POWER(x, y)
POWER(x, y) POWER(x, y) POWER(x, y) N/A POWER(x,y)
? Natural logarithm of x LN(x) LN(x) LN(x) LOG(x)
LN(x)
LN(x) LOG(x) LN(x)
LOG(x)
LN(x)
LOG(x)
LN(x) LN(x) N/A LOG(x)
? Logarithm of x, base b N/A LOG(b, x) LOG(b, x) N/A LOG(b, x) LOG(x, b) LOG(b, x) LOG(b, x) LOG(b, x) LOG(b, x) N/A ?
? Logarithm, base 10 N/A LOG10(x) LOG10(x) N/A N/A LOG10(x) LOG10(x) LOG10(x) LOG(x) LOG(x) N/A LOG(x)
? Randomize, set seed to x N/A RAND(x) N/A SET RANDOM_SEED x RAND(x) RAND(x) RAND(x) RAND(x) random()[1] SETSEED(x) N/A RANDOMIZE([x])
? Generate floating-point random number between 0 and 1 N/A RAND() RAND() RANDOMF() RAND() RAND() RAND() CAST(RAND() as float) / 2147483648 dbms_random.value (returns number >= 0 and < 1)
dbms_random.value(37, 89) (returns a random number >= 37 and < 89.)
RANDOM() RANDOM() (between −263 and 263) RND()
? Highest number in a list N/A N/A MAXVALUE(list) ? GREATEST(list) N/A GREATEST(list) GREATEST(a, b) GREATEST(list) GREATEST(list) MAX(list) MAX(list)
? Lowest number in a list N/A N/A MINVALUE(list) ? LEAST(list) N/A LEAST(list) LEAST(a, b) LEAST(list) LEAST(list) MIN(list) MIN(list)

Notes

edit
  1. The random() function in Oracle can be found in the built-in DBMS package dbms_random.