• SQL Reference
• Mathematical Functions

# Mathematical Functions

All functions accept arguments of types `int` and `float`. The return type is `float`, unless otherwise specified.

Note: A `null` argument to any mathematical function will result in a `null` return value

## #Operators

Rockset supports the basic arithmetic operators shown below on any combination of `int` and `float` values.

OperatorDescription
`+ x`positive sign
`- x`negative sign
`x + y`addition
`x - y`subtraction
`x * y`multiplication
`x / y`division
`x % y`modulo (remainder)

## #Basic Functions

### #ABS

`ABS(x)`

Returns absolute value of `x`. Return type is the same as input.

`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`.

`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`.

`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`.

`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.

`SELECT`
`    ROUND(2.4)`
`2.0`
`SELECT`
`    ROUND(2.6)`
`3.0`
`SELECT`
`    ROUND(3)`
`3.0`
`SELECT`
`    ROUND(null)`
`null`

### #GREATEST

`GREATEST(a, b, c, ...)`

Returns the argument that is greater than or equal to all other arguments.

`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.

`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.

`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)`.

`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.

`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`.

`SELECT`
`    FROM_BASE('101', 2)`
`5`
`SELECT`
`    FROM_BASE('ab', 16)`
`171`
`SELECT`
`    FROM_BASE('-21', 8)`
`-17`

## #Log and Exponential Functions

### #EXP

`EXP(x)`

Computes `e` to the power of `x`.

`SELECT`
`    EXP(1)`
`2.718281828459045`
`SELECT`
`    EXP(0)`
`1.0`
`SELECT`
`    EXP(null)`
`null`

### #POW

`POW(x, y)`

Computes `x` to the power of `y`.

`SELECT`
`    POW(2, 5)`
`32.0`
`SELECT`
`    POW(5, 0)`
`1.0`
`SELECT`
`    POW(5, null)`
`null`

### #POWER

`POWER(x, y)`

Alias of `POW`.

### #SQRT

`SQRT(x)`

Computes the square root of `x`.

`SELECT`
`    SQRT(4)`
`2.0`
`SELECT`
`    SQRT(1)`
`1.0`
`SELECT`
`    SQRT(null)`
`null`

### #LN

`LN(x)`

Computes the natural logarithm of `x`.

`SELECT`
`    LN(10)`
`2.302585092994046`
`SELECT`
`    LN(EXP(5))`
`5.0`
`SELECT`
`    LN(null)`
`null`
`SELECT`
`    LN(0)`
`Error: 'division by zero'`

### #LOG

`LOG(x)`

Alias of `LN`.

### #LOG10

`LOG10(x)`

Computes the base-10 logarithm of `x`.

`SELECT`
`    LOG10(100)`
`2.0`
`SELECT`
`    LOG10(1)`
`0.0`
`SELECT`
`    LOG10(null)`
`null`
`SELECT`
`    LOG10(0)`
`Error: 'division by zero'`

### #LOG2

`LOG2(x)`

Computes the base-2 logarithm of `x`.

`SELECT`
`    LOG2(32)`
`5.0`
`SELECT`
`    LOG2(1)`
`0.0`
`SELECT`
`    LOG2(null)`
`null`
`SELECT`
`    LOG2(0)`
`Error: 'division by zero'`

## #Trigonometric Functions

### #ACOS

`ACOS(x)`

Computes the arc cosine of `x`.

`SELECT`
`    ACOS(0.7)`
`0.7953988301841436`

### #ACOSH

`ACOSH(x)`

Computes the inverse hyperbolic cosine of `x`.

`SELECT`
`    ACOSH(90)`
`5.192925985263684`

### #ASIN

`ASIN(x)`

Computes the arc sine of `x`.

`SELECT`
`    ASIN(0.7)`
`0.775397496610753`

### #ASINH

`ASINH(x)`

Computes the inverse hyperbolic sine of `x`.

`SELECT`
`    ASINH(0.7)`
`0.6526665660823557`

### #ATAN

`ATAN(x)`

Computes the arc tangent of `x`.

`SELECT`
`    ATAN(0.7)`
`0.6107259643892086`

### #ATAN2

`ATAN2(y, x)`

Computes the arc tangent of `y / x`, but with proper sign for quadrant correction. That is, correctly computes the angle `θ` when converting from the Cartesian coordinates `(x, y)` to the polar coordinates `(r, θ)`.

`SELECT`
`    ATAN2(5, 3)`
`1.0303768265243125`

### #ATANH

`ATANH(x)`

Computes the inverse hyperbolic tangent of `x`.

`SELECT`
`    ATANH(0.7)`
`0.8673005276940531`

### #COS

`COS(x)`

Computes the cosine of `x`.

`SELECT`
`    COS(0.7)`
`0.7648421872844885`

### #COSH

`COSH(x)`

Computes the hyperbolic cosine of `x`.

`SELECT`
`    COSH(0.7)`
`1.255169005630943`

### #HYPOT

`HYPOT(x, y)`

Computes `SQRT(x*x + y*y)`, that is, the length of the hypothenuse of a right-angled triangle with sides of lengths `x` and `y`. This is also the distance between the point at coordinates `(x, y)` and origin.

`SELECT`
`    HYPOT(3, 4)`
`5.0`

### #SIN

`SIN(x)`

Computes the sine of `x`.

`SELECT`
`    SIN(0.7)`
`0.644217687237691`

### #SINH

`SINH(x)`

Computes the hyperbolic sine of `x`.

`SELECT`
`    SINH(0.7)`
`0.7585837018395334`

### #TAN

`TAN(x)`

Computes the tangent of `x`.

`SELECT`
`    TAN(0.7)`
`0.8422883804630794`

### #TANH

`TANH(x)`

Computes the hyperbolic tangent of `x`.

`SELECT`
`    TANH(0.7)`
`0.6043677771171636`

## #Bitwise Functions

### #BIT_COUNT

`BIT_COUNT(x, bits)`

Count the number of bits set in `x` (treated as `bits`-bit signed integer) in 2’s complement representation.

`-- 15 = '1111' in binary`
`SELECT`
`    BIT_COUNT(15, 4)`
`4`

### #BITWISE_AND

`BITWISE_AND(x, y)`

Returns the bitwise AND of `x` and `y` in 2’s complement representation.

`-- 10101 & 111 = 101`
`SELECT`
`    BITWISE_AND(37, 7)`
`5`

### #BITWISE_OR

`BITWISE_OR(x, y)`

Returns the bitwise OR of `x` and `y` in 2’s complement representation.

`-- 10001 | 111 = 10111`
`SELECT`
`    BITWISE_OR(33, 7)`
`39`

### #BITWISE_NOT

`BITWISE_NOT(x)`

Returns the bitwise NOT of `x` in 2’s complement representation.

`SELECT`
`    BITWISE_NOT(4)`
`-5`

### #BITWISE_XOR

`BITWISE_XOR(x, y)`

Returns the bitwise XOR of `x` and `y` in 2’s complement representation.

`-- 10101 | 01010 = 11111`
`SELECT`
`    BITWISE_XOR(21, 10)`
`31`