SQL dialects reference/Functions and expressions/Math functions/Numeric functions
Numeric functions
| ANSI/ISO SQL Level | Feature-ID | Function | DB2 | SQLite | MySQL | Template:PostgreSQL ref | Firebird | OpenLink Virtuoso | Oracle | MSSQL |
|---|---|---|---|---|---|---|---|---|---|---|
| SQL:2003/200n | T441 | Absolute value of x | ABS(x) | ABS(x) | Template:MySQL ref | ABS(x) | ABS(x) | ABS(x) | Template:Oracle ref | ABS(x) |
| Sign of number x | SIGN(x) | Template:N/a | Template:MySQL ref | SIGN(x) | SIGN(x)[1] | SIGN(x) | Template:Oracle ref | SIGN(x) | ||
| SQL:2003/200n | T441 | Modulus (remainder) of | MOD(x, y) | x % y | Template:MySQL ref | x % y MOD(x, y) |
MOD(x,y) | MOD(x,y) | Template:Oracle ref | x % y |
| SQL:2003/200n | T621 | Smallest integer >= x | CEILING(x) CEIL(x) |
Template:N/a | Template:MySQL ref | CEILING(x) CEIL(x) |
CEILING(x) | CEILING(x) | Template:Oracle ref | CEILING(x) |
| SQL:2003/200n | T621 | Largest integer <= x | FLOOR(x) | Template:N/a | Template:MySQL ref | 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]) | ||
| Truncate x to n decimal places | TRUNCATE(x, n) TRUNC(x, n) |
Template:N/a | TRUNCATE(x[, dn) | TRUNC(x[, y]) | Template:N/a | Template:N/a | TRUNC | Template:N/a | ||
| SQL:2003/200n | T621 | Square root | SQRT(x) | Template:N/a | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) |
| SQL:2003/200n | T621 | Exponent of x () | EXP(x) | Template:N/a | EXP(x) | EXP(x) | ? | EXP(x) | EXP(x) | EXP(x) |
| SQL:2003/200n | T621 | Power () | POWER(x, y) | Template:N/a | POWER(x, y) POW(x, y) |
POWER(x, y) | POWER(x, y)[1] | POWER(x,y) | POWER(x, y) | POWER(x, y) |
| SQL:2003/200n | T621 | Natural logarithm of x | LN(x) | Template:N/a | LN(x) | LN(x) | LN(x) | LOG(x) | LN(x) | LOG(x) |
| Logarithm, any base | LOG(b, x) | Template:N/a | LOG(b, x) | LOG(b, x) | LOG(b, x) | LOG(x) | LOG(x) | Template:N/a | ||
| Logarithm, base 10 | LOG10(x) | Template:N/a | LOG10(x) | LOG(x) | LOG10(x) | LOG(x) | LOG(x) | LOG10(x) | ||
| Randomize, set seed to x | RAND(x) | Template:N/a | RAND(x) | SETSEED(x) | ? | RANDOMIZE([x]) | random()[2] | RAND(x) | ||
| Generate random number | RAND() | RANDOM() | RAND() | RANDOM() | RAND() | RND() | Template:N/a | RAND() | ||
| Highest number in list | Template:N/a | MAX(list) | GREATEST(list) | GREATEST(list) | MAX() | MAX(list) | GREATEST(list) | Template:N/a | ||
| Lowest number in list | Template:N/a | MIN(list) | LEAST(list) | LEAST(list) | MIN(list) | MIN(list) | LEAST(list) | Template:N/a | ||
| SQL-92 | F261-04 | Convert number if NULL | COALESCE | COALESCE | COALESCE | COALESCE | ISNULL | COALESCE | COALESCE |