Numeric functions

ABS(numeric_exp)

Returns the absolute value of numeric_exp. For example:

SELECT ABS(-1.0), ABS(0.0), ABS(1.0) FROM emp WHERE empno = 1;

This returns 3 result columns with values 1, 0 and 1.

ACOS(float_exp)

Returns the arccosine of float_exp as an angle, expressed in radians. For example:

SELECT ACOS(-1) FROM emp WHERE empno = 1;

This returns 3.14159.

ASIN(float_exp)

Returns the arcsine of float_exp as an angle, expressed in radians. For example:

SELECT ASIN(-1.0) FROM emp WHERE empno = 1;

This returns -1.57079.

ATAN(float_exp)

Returns the arctangent of float_exp as an angle, expressed in radians. For example:

SELECT ATAN(45.0) FROM emp WHERE empno = 1;

This returns 1.54857.

ATAN2(float_exp1, float_exp2)

Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians. For example:

SELECT ATAN2(35.175, 129.44) FROM emp WHERE empno = 1;

This returns 0.2653399.

CEILING(numeric_exp)

Returns the smallest integer greater than or equal to numeric_exp. The return value is of the same data type as the input parameter. For example:

SELECT CEILING(123.45), CEILING(-123.45), CEILING(0.0) FROM emp WHERE empno = 1;

This returns 124, -123 and 0.

COS(float_exp)

Returns the cosine of float_exp, where float_exp is an angle expressed in radians. For example:

SELECT COS(14.78) FROM emp WHERE empno = 1;

This returns -0.59946542.

COT(float_exp)

Returns the cotangent of float_exp, where float_exp is an angle expressed in radians. For example:

SELECT COT(124.78) FROM emp WHERE empno = 1;

This returns -0.82045588.

DEGREES(numeric_exp)

Returns the number of degrees converted from numeric_exp radians. For example:

SELECT DEGREES(3.143) FROM emp WHERE empno = 1;

This returns 180.0806.

EXP(float_exp)

Returns the exponential value of float_exp. For example:

SELECT EXP(378.615) FROM emp WHERE empno = 1;

This returns 2.69404760606322E+164

FLOOR(numeric_exp)

Returns the largest integer less than or equal to numeric_exp. The return value is of the same data type as the input parameter. For example:

SELECT FLOOR(123.45), FLOOR(-123.45) FROM emp WHERE empno = 1;

This returns 123 and -124.

LOG(float_exp)

Returns the natural logarithm of float_exp. For example:

This returns 1.64396358.

LOG10(float_exp)

Returns the base 10 logarithm of float_exp. For example:

SELECT LOG10(145.175643) FROM emp WHERE empno = 1;

This returns 2.161893758.

SELECT LOG10(0), LOG10(-1), LOG10(1) FROM emp WHERE empno = 1;

This returns –1.#INF, -1.#IND and 0

MOD(integer_exp1, integer_exp2)

Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. For example:

SELECT mod(empno, 2) FROM emp WHERE empno = 11;

This returns 1.

PI( )

Returns the constant value of pi as a floating-point value. For example:

SELECT PI() FROM emp WHERE empno = 1;

This returns 3.14159265358979.

POWER(numeric_exp, integer_exp)

Returns the value of numeric_exp to the power of integer_exp. For example:

SELECT POWER(2, -5), POWER(2, 5) FROM emp WHERE empno = 1;

This returns 0, 32.

RADIANS(numeric_exp)

Returns the number of radians converted from numeric_exp degrees. For example:

SELECT RADIANS(45.0) FROM emp WHERE empno = 1;

This returns 0.785398.

RAND([integer_exp])

Returns a random floating-point value using integer_exp as a mandatory seed value. For example:

SELECT RAND(0) FROM emp WHERE empno = 1;

This returns 38. If integer_exp is not specified, an error occurs.

ROUND(numeric_exp, integer_exp)

Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point. For example:

SELECT ROUND(123.344, 2), ROUND(123.345, 2) FROM emp WHERE empno = 1;

This returns 123.34 and 123.35.

SELECT ROUND(748.58, -1), ROUND(748.58, -2), ROUND(748.58, 3), FROM emp WHERE empno = 1;

This returns 750, 700 and 1000.

SIGN(numeric_exp)

Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. For example:

SELECT SIGN(empno) FROM emp WHERE empno = 11;

This returns 1.

SELECT SIGN(-1 * empno), SIGN(0) FROM emp WHERE empno = 1;

This returns two result columns with values –1 and 0.

SIN(float_exp)

Returns the sine of float_exp, where float_exp is an angle expressed in radians. For example:

SELECT SIN(1.570796) FROM emp WHERE empno = 11;

This returns 0.999999.

SQRT(float_exp)

Returns the square root of float_exp. For example:

SELECT SQRT(45.35) FROM emp WHERE empno = 11;

This returns 6.7342.

TAN(float_exp)

Returns the tangent of float_exp, where float_exp is an angle expressed in radians. For example:

SELECT TAN(0.785398) FROM emp WHERE empno = 11;

This returns 0.999999.

TRUNCATE(numeric_exp, integer_exp)

Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point.