doc-exports/docs/dws/dev/dws_06_0034.html

Mathematical Functions and Operators

Numeric 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)

Numeric operation functions

• abs(x)

  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)

• acos(x)

  Description: Arc cosine

  Return type: double precision

  For example:

  1 2 3 4 5

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

• asin(x)

  Description: Arc sine

  Return type: double precision

  For example:

  1 2 3 4 5

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

• atan(x)

  Description: Arc tangent

  Return type: double precision

  For example:

  1 2 3 4 5

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

• atan2(y, x)

  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)

• bitand(integer, integer)

  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)

• cbrt(dp)

  Description: Cubic root

  Return type: double precision

  For example:

  1 2 3 4 5

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

• ceil(x)

  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)

• ceiling(dp or numeric)

  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)

• cos(x)

  Description: Cosine

  Return type: double precision

  For example:

  1 2 3 4 5

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

• cot(x)

  Description: Cotangent

  Return type: double precision

  For example:

  1 2 3 4 5

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

• degrees(dp)

  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)

• div(y numeric, x numeric)

  Description: Integer part of y/x

  Return type: numeric

  For example:

  1 2 3 4 5

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

• exp(x)

  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)

• floor(x)

  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)

• radians(dp)

  Description: Converts angles to radians.

  Return type: double precision

  For example:

  1 2 3 4 5

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

• random()

  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)

• ln(x)

  Description: Natural logarithm

  Return type: same as the input

  For example:

  1 2 3 4 5

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

• log(x)

  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)

• log(b numeric, x numeric)

  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)

• mod(x,y)

  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)

• pi()

  Description: π constant value

  Return type: double precision

  For example:

  1 2 3 4 5

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

• power(a double precision, b double precision)

  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)

• round(x)

  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.

• round(v numeric, s int)

  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)

• setseed(dp)

  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)

• sign(x)

  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)

• sin(x)

  Description: Sine

  Return type: double precision

  For example:

  1 2 3 4 5

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

• sqrt(x)

  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)

• tan(x)

  Description: Tangent

  Return type: double precision

  For example:

  1 2 3 4 5

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

• trunc(x)

  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)

• trunc(v numeric, s int)

  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)

• width_bucket(op numeric, b1 numeric, b2 numeric, count int)

  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)

• width_bucket(op dp, b1 dp, b2 dp, count int)

  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)

Parent topic: Functions and Operators