Jump to content

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

From Wikibooks, open books for an open world

Numeric functions

[edit | edit source]

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 | edit source]
  1. The random() function in Oracle can be found in the built-in DBMS package dbms_random.