ABS
ABS(x) Returns absolute value of x. Return type is the same as input.
| SQL command | Result |
|---|---|
SELECT ABS(5) | 5 |
SELECT ABS(-5) | 5 |
SELECT ABS(0) | 0 |
SELECT ABS(null) | null |
FLOOR
FLOOR(x) Returns the largest integral value that is not greater than x.
| SQL command | Result |
|---|---|
SELECT FLOOR(2.4) | 2.0 |
SELECT FLOOR(2.6) | 2.0 |
SELECT FLOOR(2) | 2.0 |
SELECT FLOOR(null) | null |
TRUNCATE
TRUNCATE(x) Rounds x toward zero, returning the nearest integral value that is not larger in magnitude than x.
| SQL command | Result |
|---|---|
SELECT TRUNCATE(2.4) | 2.0 |
SELECT TRUNCATE(2.6) | 2.0 |
SELECT TRUNCATE(-2.4) | -2.0 |
TRUNC
TRUNC(x) Alias of TRUNCATE.
CEIL
CEIL(x) Returns the smallest integral value that is not less than x.
| SQL command | Result |
|---|---|
SELECT CEIL(2.4) | 3.0 |
SELECT CEIL(2.6) | 3.0 |
SELECT CEIL(3) | 3.0 |
SELECT CEIL(null) | null |
CEILING
CEILING(x) Alias of CEIL.
ROUND
ROUND(x) Returns the integral value that is nearest to x, with halfway cases rounded away from zero.
| SQL command | Result |
|---|---|
SELECT ROUND(2.4) | 2.0 |
SELECT ROUND(2.6) | 3.0 |
SELECT ROUND(3) | 3.0 |
SELECT ROUND(null) | null |
ROUND(x, y) Returns the value of x that is rounded to the nearest decimal place y, with halfway cases rounded away from zero.
| SQL command | Result |
|---|---|
SELECT ROUND(2.467, 2) | 2.47 |
SELECT ROUND(2.55, 1) | 2.6 |
SELECT ROUND(3, 1) | 3.0 |
SELECT ROUND(null, 3) | null |
GREATEST
GREATEST(a, b, c, ...) Returns the argument that is greater than or equal to all other arguments.
| SQL command | Result |
|---|---|
SELECT GREATEST(1) | 1 |
SELECT GREATEST(1, 2, 3) | 3 |
SELECT GREATEST(1, null, 3) | null |
LEAST
LEAST(a, b, c, ...) Returns the argument that is less than or equal to all other arguments.
| SQL command | Result |
|---|---|
SELECT LEAST(1) | 1 |
SELECT LEAST(1, 2, 3) | 1 |
SELECT LEAST(1, null, 3) | null |
SIGN
SIGN(x) Returns sign of x as an integer: -1 if x is negative, 0 if x is zero, 1 if x is positive.
| SQL command | Result |
|---|---|
SELECT SIGN(5) | 1 |
SELECT SIGN(-5) | -1 |
SELECT SIGN(0) | 0 |
SELECT SIGN(null) | null |
RAND
RAND() Returns a pseudo-random value in the range [0.0, 1.0).
| SQL command | Result |
|---|---|
SELECT RAND() | 0.08367730533758584 |
SELECT RAND() | 0.20026947414188864 |
SELECT RAND() | 0.5600549036289146 |
IS_NAN
IS_NAN(x) Returns true if the input is a floating point Not-A-Number, for instance, due to dividing zero by zero or taking the square root of a negative number. Non-numeric inputs yield null.
| SQL command | Result |
|---|---|
SELECT IS_NAN(CAST('NaN' as FLOAT)) | true |
SELECT IS_NAN(1.0) | false |
FROM_BASE
FROM_BASE(string, base) Returns the value of string interpreted as a number in base.
| SQL command | Result |
|---|---|
SELECT FROM_BASE('101', 2) | 5 |
SELECT FROM_BASE('ab', 16) | 171 |
SELECT FROM_BASE('-21', 8) | -17 |