SQL Mathematical Functions and Operators

You can use mathematical operators in the target list, in the WHERE clause of a SELECT statement, or anywhere else a numeric result may be appropriate. This sometimes will include the ORDER BY clause, a JOIN qualifier, or a GROUP BY clause.

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.
SELECT ceil(98.6) FROM dual;
 ceiling
 --------
   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.
SELECT floor (98.6) FROM dual; 
 floor
 -----
   98
MOD(dividend, divisor) Returns remainder of the division from two integer values.
SELECT MOD(20,3) FROM dual;
 -
 2
PI()

Returns a constant value for pi.

SELECT pi() FROM 
       pi
 -----------------
 3.141592653589793
POWER(a, b) Takes 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 value in the range 0.0 <= x < 1.0.
SELECT rand() FROM dual;
       rand
 ------------------
 0.1933957548781278
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.
SELECT round(42.4) FROM dual;
 round
 ------
   42
ROUND(dpnumeric, s ) Round dp (or numeric) to s decimal places.
SELECT round(42.4382,2) FROM dual;
 round
 ------
 42.44
SQRT(dp | numeric) Square root of dp (or numeric) up to 15 decimal points.
SELECT sqrt(2.0) FROM dual;
 sqrt
 -----------------
 1.414213562373095

Trigonometric Functions

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). It is displayed in the format of the value on the left hand side of the operator. That is, if the value on the left hand side of the operator is BINARY, the result will be displayed as BINARY and if the result on the left hand side of the operator is an INTEGER, the result will be displayed as an INTEGER.

Examples

Insert the values: integer 79 and binary 0x18, represented in binary as:
0100 1111

0001 1000

CREATE TABLE testtab (int_col INTEGER, bin_col BINARY(8));
INSERT INTO testtab VALUES (79, 0x18);
 
SELECT int_col | bin_col FROM testtab;
 --
 95
 
SELECT int_col & bin_col FROM testtab;
 -
 8
 
SELECT int_col ^ bin_col FROM testtab;
 --
 87
 
SELECT bin_col | int_col FROM testtab;
 --
 5F 
 
SELECT bin_col  & int_col FROM testtab;
 --
 08
 
SELECT bin_col  ^ int_col FROM testtab;
 --
 57