Mathematical Functions and Operators

Description: Addition

For example:

1
2
3
4
5

SELECT 2+3 AS RESULT;
 result 
--------
      5
(1 row)

Description: Subtraction

For example:

1
2
3
4
5

SELECT 2-3 AS RESULT;
 result 
--------
     -1
(1 row)

Description: multiply

For example:

1
2
3
4
5

SELECT 2*3 AS RESULT;
 result 
--------
      6
(1 row)

Description: Division (The result is not rounded.)

For example:

1
2
3
4
5

SELECT 4/2 AS RESULT;
 result 
--------
      2
(1 row)

1
2
3
4
5

SELECT 4/3 AS RESULT;
      result      
------------------
 1.33333333333333
(1 row)

Description: Positive/negative

For example:

1
2
3
4
5

SELECT -2 AS RESULT;
 result 
--------
     -2
(1 row)

Description: Model (to obtain the remainder)

For example:

1
2
3
4
5

SELECT 5%4 AS RESULT;
 result 
--------
      1
(1 row)

Description: Absolute value

For example:

1
2
3
4
5

SELECT @ -5.0 AS RESULT;
 result 
--------
    5.0
(1 row)

Description: Power (exponent calculation)

In MySQL-compatible mode, this operator means exclusive or. For details, see operator # in Bit String Functions and Operators.

For example:

1
2
3
4
5

SELECT 2.0^3.0 AS RESULT;
       result       
--------------------
 8.0000000000000000
(1 row)

Description: Square root

For example:

1
2
3
4
5

SELECT |/ 25.0 AS RESULT;
 result 
--------
      5
(1 row)

Description: Cubic root

For example:

1
2
3
4
5

SELECT ||/ 27.0 AS RESULT;
 result 
--------
      3
(1 row)

Description: Factorial

For example:

1
2
3
4
5

SELECT 5! AS RESULT;
 result 
--------
    120
(1 row)

Description: Factorial (prefix operator)

For example:

1
2
3
4
5

SELECT !!5 AS RESULT;
 result 
--------
    120
(1 row)

Description: Binary AND

For example:

1
2
3
4
5

SELECT 91&15  AS RESULT;
 result 
--------
     11
(1 row)

Description: Binary OR

For example:

1
2
3
4
5

SELECT 32|3  AS RESULT;
 result 
--------
     35
(1 row)

Description: Binary XOR

For example:

1
2
3
4
5

SELECT 17#5  AS RESULT;
 result 
--------
     20
(1 row)

Description: Binary NOT

For example:

1
2
3
4
5

SELECT ~1 AS RESULT;
 result 
--------
     -2
(1 row)

Description: Binary shift left

For example:

1
2
3
4
5

SELECT 1<<4 AS RESULT;
 result 
--------
     16
(1 row)

Description: Binary shift right

For example:

1
2
3
4
5

SELECT 8>>2 AS RESULT;
 result 
--------
      2
(1 row)

Description: Absolute value

Return type: same as the input

For example:

1
2
3
4
5

SELECT abs(-17.4);
 abs
------
 17.4
(1 row)

Description: Arc cosine

Return type: double precision

For example:

1
2
3
4
5

SELECT acos(-1);
       acos       
------------------
 3.14159265358979
(1 row)

Description: Arc sine

Return type: double precision

For example:

1
2
3
4
5

SELECT asin(0.5);
       asin       
------------------
 .523598775598299
(1 row)

Description: Arc tangent

Return type: double precision

For example:

1
2
3
4
5

SELECT atan(1);
       atan       
------------------
 .785398163397448
(1 row)

Description: Arc tangent of y/x

Return type: double precision

For example:

1
2
3
4
5

SELECT atan2(2, 1);
      atan2
------------------
 1.10714871779409
(1 row)

Description: Performs AND (&) operation on two integers.

Return type: bigint

For example:

1
2
3
4
5

SELECT bitand(127, 63);
 bitand 
--------
     63
(1 row)

Description: Cubic root

Return type: double precision

For example:

1
2
3
4
5

SELECT cbrt(27.0);
 cbrt
------
    3
(1 row)

Description: Minimum integer greater than or equal to the parameter

Return type: integer

For example:

1
2
3
4
5

SELECT ceil(-42.8);
 ceil 
------
  -42
(1 row)

Description: Minimum integer (alias of ceil) greater than or equal to the parameter

Return type: same as the input

For example:

1
2
3
4
5

SELECT ceiling(-95.3);
 ceiling
---------
     -95
(1 row)

Description: Cosine

Return type: double precision

For example:

1
2
3
4
5

SELECT cos(-3.1415927);
        cos        
-------------------
 -.999999999999999
(1 row)

Description: Cotangent

Return type: double precision

For example:

1
2
3
4
5

SELECT cot(1);
       cot
------------------
 .642092615934331
(1 row)

Description: Converts radians to angles.

Return type: double precision

For example:

1
2
3
4
5

SELECT degrees(0.5);
     degrees
------------------
 28.6478897565412
(1 row)

Description: Integer part of y/x

Return type: numeric

For example:

1
2
3
4
5

SELECT div(9,4);
 div
-----
   2
(1 row)

Description: Natural exponent

Return type: same as the input

For example:

1
2
3
4
5

SELECT exp(1.0);
        exp         
--------------------
 2.7182818284590452
(1 row)

Description: Not larger than the maximum integer of the parameter

Return type: same as the input

For example:

1
2
3
4
5

SELECT floor(-42.8);
 floor 
-------
   -43
(1 row)

Description: Converts angles to radians.

Return type: double precision

For example:

1
2
3
4
5

SELECT radians(45.0);
     radians
------------------
 .785398163397448
(1 row)

Description: Random number between 0.0 and 1.0

Return type: double precision

For example:

1
2
3
4
5

SELECT random();
      random
------------------
 .824823560658842
(1 row)

Description: Natural logarithm

Return type: same as the input

For example:

1
2
3
4
5

SELECT ln(2.0);
        ln         
-------------------
 .6931471805599453
(1 row)

Description: Logarithm with 10 as the base

• In the ORA- or TD-compatible mode, this operator means the logarithm with 10 as the base.
• In the MySQL-compatible mode, this operator means the natural logarithm.

Return type: same as the input

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- ORA-compatible mode
SELECT log(100.0);
        log         
--------------------
 2.0000000000000000
(1 row)
-- TD-compatible mode
SELECT log(100.0);
        log
--------------------
 2.0000000000000000
(1 row)
-- MySQL-compatible mode
SELECT log(100.0);
        log
--------------------
 4.6051701859880914
(1 row)

Description: Logarithm with b as the base

Return type: numeric

For example:

1
2
3
4
5

SELECT log(2.0, 64.0);
        log         
--------------------
 6.0000000000000000
(1 row)

Description:

Remainder of x/y (model)

If x equals to 0, y is returned.

Return type: same as the parameter type

For example:

1
2
3
4
5

SELECT mod(9,4);
 mod 
-----
   1
(1 row)

1
2
3
4
5

SELECT mod(9,0);
 mod 
-----
   9
(1 row)

Description: π constant value

Return type: double precision

For example:

1
2
3
4
5

SELECT pi();
        pi
------------------
 3.14159265358979
(1 row)

Description: b power of a

Return type: double precision

For example:

1
2
3
4
5

SELECT power(9.0, 3.0);
        power         
----------------------
 729.0000000000000000
(1 row)

Description: Integer closest to the input parameter

Return type: same as the input

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT round(42.4);
 round 
-------
    42
(1 row)

SELECT round(42.6);
 round 
-------
    43
(1 row)

When the round function is invoked, the numeric type is rounded to zero. While on most computers, the real number and the double-precision number are rounded to the nearest even number.

Description: s digits are kept after the decimal point.

Return type: numeric

For example:

1
2
3
4
5

SELECT round(42.4382, 2);
 round
-------
 42.44
(1 row)

Description: Sets seed for the following random() invoking (between -1.0 and 1.0, inclusive).

Return type: void

For example:

1
2
3
4
5

SELECT setseed(0.54823);
 setseed
---------

(1 row)

Description: returns symbols of this parameter.

The return value type:-1 indicates negative. 0 indicates 0, and 1 indicates a positive number.

For example:

1
2
3
4
5

SELECT sign(-8.4);
 sign 
------
   -1
(1 row)

Description: Sine

Return type: double precision

For example:

1
2
3
4
5

SELECT sin(1.57079);
       sin        
------------------
 .999999999979986
(1 row)

Description: Square root

Return type: same as the input

For example:

1
2
3
4
5

SELECT sqrt(2.0);
       sqrt        
-------------------
 1.414213562373095
(1 row)

Description: Tangent

Return type: double precision

For example:

1
2
3
4
5

SELECT tan(20);
       tan        
------------------
 2.23716094422474
(1 row)

Description: truncates (the integral part).

Return type: same as the input

For example:

1
2
3
4
5

SELECT trunc(42.8);
 trunc 
-------
    42
(1 row)

Description: Truncates a number with s digits after the decimal point.

Return type: numeric

For example:

1
2
3
4
5

SELECT trunc(42.4382, 2);
 trunc
-------
 42.43
(1 row)

Description: Returns a bucket to which the operand will be assigned in an equidepth histogram with count buckets, ranging from b1 to b2.

Return type: int

For example:

1
2
3
4
5

SELECT width_bucket(5.35, 0.024, 10.06, 5);
 width_bucket
--------------
            3
(1 row)

Description: Returns a bucket to which the operand will be assigned in an equidepth histogram with count buckets, ranging from b1 to b2.

Return type: int

For example:

1
2
3
4
5

SELECT width_bucket(5.35, 0.024, 10.06, 5);
 width_bucket
--------------
            3
(1 row)