Rounding and Truncation Functions

Adjust numerical values by rounding or truncating.

CEIL( <expr> )

Returns the smallest integer which is greater than or equal to the specified numerical expression.

> select ceil(2.34);
3

FLOOR( <expr> )

Returns the largest integer which is less than or equal to the specified numerical expression.

> select floor(6.34);
6

ROUND( <expr> [, <scale> ] )

Returns the numeric value, rounded off to a given number of decimal places, of the input numeric expression. If the scale factor is not specified, it will round off to the nearest integer.

scale - optional numeric expression, representing the number of decimal places to be rounded off to in a given numeric expression. Default is 0.

> select round(5.678);
6
> select round(5.678, 2);
5.68

ABS( <expr> )

Returns the absolute value of the specified numeric expression.

> select abs(-100);
100

SIGN( <expr> )

The SIGN function returns the sign of its argument:

• Returns -1 if the argument is negative

• Returns 1 if the argument is positive

• Returns 0 if the argument is 0

> select sign(-100);
-1

MOD( <expr1>, <expr2> )

Returns the remainder of expression-1 divided by expression-2

Support datatype: INT/DOUBLE

> select mod(17,3)
2.0