SQL Dialects Reference/Functions and expressions/Math functions/Numeric functions
From Wikibooks, open books for an open world
This page may need to be [edit] Numeric functions
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 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | Oracle | PostgreSQL | SQLite | Virtuoso |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| <= 1999 | 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) |
| Sign of number x | ? | SIGN(x) | SIGN(x)[1] | SING(x) | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | N/A | SIGN(x) | |
| <= 1999 | 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) |
MOD(x, y) | x % y MOD(x, y) |
x % y | MOD(x, y) |
| SQL:2003/200n | Smallest integer >= x | ? | CEILING(x) CEIL(x) |
CEILING(x) | CEIL(x) CEILING(x) |
CEIL(x) | CEILING(x) | CEILING(x) CEIL(x) |
CEIL(x) | CEILING(x) CEIL(x) |
N/A | CEILING(x) |
| SQL:2003/200n | Largest integer <= 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) | ? | ROUND(x, d) | ROUND(x, d)[1] | 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 | ? | TRUNCATE(x, n) TRUNC(x, n) |
N/A | TRUNCATE(x, n) TRUNC(x, n) |
TRUNC(x[, d]) | N/A | TRUNCATE(x[, dn) | TRUNC | TRUNC(x[, y]) | N/A | N/A | |
| SQL:2003/200n | Square root of x ( ) |
? | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | N/A | SQRT(x) |
| SQL:2003/200n | Exponent of x (ex) | ? | EXP(x) | ? | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | N/A | EXP(x) |
| SQL:2003/200n | Power (xy) | ? | POWER(x, y) | POWER(x, y)[1] | POWER(x, y) x ** y |
POWER(x, y) | POWER(x, y) | POW(x, y) POWER(x, y) |
POWER(x, y) | POWER(x, y) | N/A | POWER(x,y) |
| SQL:2003/200n | Natural logarithm of x | ? | LN(x) | LN(x) | LOG(x) LN(x) |
LN(x) | LOG(x) | LN(x) LOG(x) |
LN(x) | LN(x) | N/A | LOG(x) |
| Logarithm of x, base b | ? | LOG(b, x) | LOG(b, x) | N/A | LOG(b, x) | N/A | LOG(b, x) | LOG(b, x) | LOG(b, x) | N/A | ? | |
| Logarithm, base 10 | ? | LOG10(x) | LOG10(x) | N/A | N/A | LOG10(x) | LOG10(x) | LOG(x) | LOG(x) | N/A | LOG(x) | |
| Randomize, set seed to x | ? | RAND(x) | ? | SET RANDOM_SEED x | RAND(x) | RAND(x) | RAND(x) | random()[2] | SETSEED(x) | N/A | RANDOMIZE([x]) | |
| Generate floating-point random number between 0 and 1 | ? | RAND() | RAND() | RANDOMF() | RAND() | RAND() | RAND() | N/A | RANDOM() | RANDOM() | RND() | |
| <= 1999 | Highest number in a list | MAX(list) | N/A | MAX() | ? | GREATEST(list) | N/A | GREATEST(list) | GREATEST(list) | GREATEST(list) | MAX(list) | MAX(list) |
| <= 1999 | Lowest number in a list | MIN(list) | N/A | MIN(list) | ? | LEAST(list) | N/A | LEAST(list) | LEAST(list) | LEAST(list) | MIN(list) | MIN(list) |
