Last modified on 22 May 2014, at 20:19

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

Numeric functionsEdit

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 x / y 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) TRUNCATE(x [, dn]) 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) SQRT(x) N/A SQRT(x)
 ? Exponent of x (e^x) EXP(x) EXP(x)  ? EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) EXP(x) N/A EXP(x)
 ? Power (x^y) 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)
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) N/A LOG(b, x) N/A 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)
SQLRAND(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() RAND() N/A RANDOM() RANDOM() RND()
 ? Highest number in a list MAX(list) N/A MAXVALUE(list)  ? GREATEST(list) N/A GREATEST(list)  ? GREATEST(list) GREATEST(list) MAX(list) MAX(list)
 ? Lowest number in a list MIN(list) N/A MINVALUE(list)  ? LEAST(list) N/A LEAST(list)  ? LEAST(list) LEAST(list) MIN(list) MIN(list)

NotesEdit

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