SQL Dialects Reference/Functions and expressions/Math functions/Numeric functions
From Wikibooks, the open-content textbooks collection
[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.
| ANSI/ISO SQL Level | Feature-ID | Function | DB2 | SQLite | MySQL | PostgreSQL | Firebird | OpenLink Virtuoso | Oracle | MSSQL | Linter | EJB-QL |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL:2003/200n | T441 | Absolute value of 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) | N/A | SIGN(x) | SIGN(x) | SIGN(x)[1] | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | |||
| SQL:2003/200n | T441 | Modulus (remainder) of x / y | MOD(x, y) | x % y | x % y MOD(x, y) |
x % y MOD(x, y) |
MOD(x,y) | MOD(x,y) | MOD(x, y) | x % y | MOD(x, y) | |
| SQL:2003/200n | T621 | Smallest integer >= x | CEILING(x) CEIL(x) |
N/A | CEILING(x) CEIL(x) |
CEILING(x) CEIL(x) |
CEILING(x) | CEILING(x) | CEIL(x) | CEILING(x) | CEIL(x) | |
| SQL:2003/200n | T621 | Largest integer <= x | FLOOR(x) | N/A | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | |
| Round x (to precision of d digits) | ROUND(x, d) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x,d)[1] | ROUND(x) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x[, d]) | |||
| Truncate x to n decimal places | TRUNCATE(x, n) TRUNC(x, n) |
N/A | TRUNCATE(x[, dn) | TRUNC(x[, y]) | N/A | N/A | TRUNC | N/A | TRUNC(x[, d]) | |||
| SQL:2003/200n | T621 | Square root | SQRT(x) | N/A | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) |
| SQL:2003/200n | T621 | Exponent of x (ex) | EXP(x) | N/A | EXP(x) | EXP(x) | ? | EXP(x) | EXP(x) | EXP(x) | EXP(x) | |
| SQL:2003/200n | T621 | Power (xy) | POWER(x, y) | N/A | POWER(x, y) POW(x, y) |
POWER(x, y) | POWER(x, y)[1] | POWER(x,y) | POWER(x, y) | POWER(x, y) | POWER(x, y) | SOMEONE HELP HERE!! |
| SQL:2003/200n | T621 | Natural logarithm of x | LN(x) | N/A | LN(x) | LN(x) | LN(x) | LOG(x) | LN(x) | LOG(x) | LN(x) | LN(x) |
| Logarithm, any base | LOG(b, x) | N/A | LOG(b, x) | LOG(b, x) | LOG(b, x) | LOG(x) | LOG(x) | N/A | LOG(b, x) | |||
| Logarithm, base 10 | LOG10(x) | N/A | LOG10(x) | LOG(x) | LOG10(x) | LOG(x) | LOG(x) | LOG10(x) | N/A | |||
| Randomize, set seed to x | RAND(x) | N/A | RAND(x) | SETSEED(x) | ? | RANDOMIZE([x]) | random()[2] | RAND(x) | RAND(x) | |||
| Generate random number | RAND() | RANDOM() | RAND() | RANDOM() | RAND() | RND() | N/A | RAND() | RAND() | |||
| Highest number in list | N/A | MAX(list) | GREATEST(list) | GREATEST(list) | MAX() | MAX(list) | GREATEST(list) | N/A | GREATEST(list) | |||
| Lowest number in list | N/A | MIN(list) | LEAST(list) | LEAST(list) | MIN(list) | MIN(list) | LEAST(list) | N/A | LEAST(list) | |||
| SQL-92 | F261-04 | Convert number if NULL | COALESCE | COALESCE | COALESCE | COALESCE | ISNULL | COALESCE | COALESCE | COALESCE |
