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

From Wikibooks, open books for an open world
Jump to: navigation, search

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 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)

Notes[edit]

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