SQL Dialects Reference/Functions and expressions/Math functions/Numeric functions
Numeric functions
edit| 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- ↑ The random() function in Oracle can be found in the built-in DBMS package dbms_random.