SQL Mathematical Functions and Operators

Mathematical functions and operators operate on numbers and return numbers. If the type of the input argument or arguments is not a number type, NuoDB enforces coercion rules as described by Data Type Conversion to convert the arguments to numbers.

Mathematical Operators

Mathematical operators affect one or two values, perform a mathematical operation, and return a value of a numeric data type.

Operator Usage Description

+

a + b

Addition of numeric quantities a and b

-

a - b

Subtraction of numeric quantity b from a

*

a * b

Multiplication of numeric quantities a and b

/

a / b

Division of numeric quantity a by b

%

a % b

Modulus, or remainder, from dividing a by b

Comparison Operators

Numeric comparison operators draw a conclusion based on two numeric values (such as whether one is larger than the other) and returns a value of type boolean, set to either true or false.

Operator Description

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

=

equal

<> or !=

no equal

Mathematical Functions

In the following table dp indicates a DOUBLE PRECISION number and numeric indicates a fixed point number.

Function Description

ABS (x)

Absolute value function. Returns the absolute value of a number. The absolute value of a number is its distance from 0 on the number line.

SELECT abs(-34.8) FROM dual;
 [ABS]
 ------
  34.8

CEIL (dp | numeric)
or
CEILING (dp | numeric)

Rounds a number up; returns the smallest integer that is greater than or equal to a given numeric expression. The return type has the same type precision and scale as the input type.

SELECT ceil(98.6) FROM dual;
 [CEIL]
 -------
   99

SELECT ceiling(95.3) FROM dual;
 [CEILING]
 ----------
     96

DEGREES (dp)

Radians to degrees

SELECT degrees(0.5) FROM dual;
     [DEGREES]
 -----------------
 28.64788975654116

FLOOR(dp | numeric)

Rounds a number down; returns the largest number that is less than or equal to the specified number. The return type has the same type precision and scale as the input type. If the input type is not a numeric type, If the input type is not a number, the type transition matrix is used for converting the input to a number. See Data Type Conversion for details on applied coercions.

SELECT floor (98.6) FROM dual;
 [FLOOR]
 --------
    98

MOD(dividend, divisor)

Returns remainder of the division from two numeric values. See Data Type Conversion for details on how NuoDB computes.

SELECT MOD(20,3) FROM dual;
 [%]
 ----
  2

PI()

Returns a constant value for pi.

SELECT pi() FROM dual;
       [PI]
 -----------------
 3.141592653589793

POWER(a, b)

Returns a double precision number representing a to the power b.

SELECT power(2, 3) FROM dual;
 [POWER]
 --------
    8

RADIANS(dp)

Degrees to radians

SELECT radians (45.0) FROM dual;
     [RADIANS]
 ------------------
 0.7853981633974483

RAND()

Random double precision value in the range 0.0 ≤ x < 1.0.

SELECT rand() FROM dual;
       [RAND]
 ------------------
 0.4449284450040766

RAND(int)

A call to RAND(int) consistently, but arbitrarily maps integers to sequences of doubles in the range [0, 1). Calling RAND() with sequential values as its argument will yield a psuedo-random sequence of uniformly distributed numbers. Be aware that RAND() is not suitable for cryptographic purposes.

ROUND(dp | numeric)

Round to nearest integer. The return type preserves the type, precision and scale of the input argument.

SELECT round(42.4) FROM dual;
 [ROUND]
 --------
    42

ROUND(dp | numeric, s )

Round dp (or numeric) to s decimal places. The return type preserves the type, precision and scale of the input argument.

SELECT round(42.4382,2) FROM dual;
 [ROUND]
 --------
 42.4400

SQRT(dp | numeric)

Square root of the input value as a double precision number.

SELECT sqrt(2.0) FROM dual;
      [SQRT]
 -----------------
 1.414213562373095

Trigonometric Functions

Trigonometric functions are defined on a double precision input value and return a double precision value. NuoDB supports the following trigonometric functions:

Function Description

ACOS(x)

inverse cosine

ASIN(x)

inverse sine

ATAN(x)

inverse tangent

ATAN2(y, x)

inverse tangent of y/x

COS(x)

cosine

COT(x)

cotangent

SIN(x)

sine

TAN(x)

tangent

Bitwise Operators

NuoDB supports the following bitwise operators:

Operator Description

&

Bitwise AND

|

Bitwise OR

^

Bitwise XOR

~

Bitwise NOT

Parameters to these operators must be of a type that can be converted to a BIGINT.

The result of any of the above operations is a 64-bit integer (a SQL BIGINT).