SAND CDBMS SQL Reference Guide
Value Expressions

 

Previous Topic:
String Functions
Chapter Index
 Next Topic:
Date/Time Functions

 

Functions


Math Functions

The math functions are used for manipulating numeric values. A math function can appear wherever a value expression is allowed. SAND CDBMS supports the following math functions:

ABS ( value-expression )
Returns the absolute value of the input expression.

Examples

Input Output
-1 1
1 1


CREATE TABLE num_table (col1 SMALLINT);
INSERT INTO num_table VALUES (-1);
1 row affected
INSERT INTO num_table VALUES (1);
1 row affected
SELECT ABS(col1) FROM num_table;
2 rows selected
1
------
     1
     1


ACOS ( value-expression )
Returns the inverse cosine (arccosine) in radians of a value between -1 and 1. Input values outside of this range will return an error. If the argument is null, this function will return a null value.

Examples

Input Output
-5.578e-01 2.16252907747110e+00
1.000 0.00000000000000e+00
0 1.57079632679490e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-5.578e-01, 1.000, 0);
1 row affected
SELECT ACOS(col1), ACOS(col2), ACOS(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  2.16252907747110e+00   0.00000000000000e+00   1.57079632679490e+00


ACOSD ( value-expression )
Same as the ACOS function, but returns the value in degrees rather than radians.

Examples

Input Output
-5.578e-01 1.23903789213413e+02
1.000 0.00000000000000e+00
0 9.00000000000000e+01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-5.578e-01, 1.000, 0);
1 row affected
SELECT ACOSD(col1), ACOSD(col2), ACOSD(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  1.23903789213413e+02   0.00000000000000e+00   9.00000000000000e+01


ACOSH ( value-expression )
Returns the hyperbolic arccosine (inverse cosine) of the input value. If the argument is null, this function will return a null value.

Examples

Input Output
1.41657979367371 8.83735870195434e-01
2162e-03 1.40581061432807e+00
1 0.00000000000000e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (1.41657979367371, 2162e-03, 1);
1 row affected
SELECT ACOSH(col1), ACOSH(col2), ACOSH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  8.83735870195434e-01   1.40581061432807e+00   0.00000000000000e+00


ASIN ( value-expression )
Returns the inverse sine (arcsine) in radians of a value between -1 and 1. Input values outside of this range will return an error. If the argument is null, this function will return a null value.

Examples

Input Output
-3.95e-1 -4.06067851057410e-01
0.000 0.00000000000000e+00
1 1.57079632679490e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-3.95e-1, 0.000, 1);
1 row affected
SELECT ASIN(col1), ASIN(col2), ASIN(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -4.06067851057410e-01   0.00000000000000e+00   1.57079632679490e+00


ASIND ( value-expression )
Same as the ASIN function, but returns the value in degrees rather than radians.

Examples

Input Output
-3.95e-1 -2.32659740615365e+01
0.000 0.00000000000000e+00
1 9.00000000000000e+01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-3.95e-1, 0.000, 1);
1 row affected
SELECT ASIND(col1), ASIND(col2), ASIND(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -2.32659740615365e+01   0.00000000000000e+00   9.00000000000000e+01


ASINH ( value-expression )
Returns the hyperbolic arcsine (inverse sine) of the input value. If the argument is null, this function will return a null value.

Examples

Input Output
-4.06067851057410e-01 -3.95663266641582e-01
1.57079 1.23339971983229e+00
1 8.81373587019543e-01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(15,14), col3 SMALLINT);
INSERT INTO num_table VALUES (-4.06067851057410e-01, 1.57079, 1);
1 row affected
SELECT ASINH(col1), ASINH(col2), ASINH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -3.95663266641582e-01   1.23339971983229e+00   8.81373587019543e-01


ATAN ( value-expression )
Returns the inverse tangent (arctangent) of the input value in radians. If the argument is null, this function will return a null value.

Examples

Input Output
2.5e-2 2.49947936189202e-02
2.5 1.10714871779409e+00
-20 -1.52083793107295e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (2.5e-2, 2.5, -20);
1 row affected
SELECT ATAN(col1), ATAN(col2), ATAN(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  2.49947936189202e-02   1.19028994968253e+00  -1.52083793107295e+00


ATAN2 ( value-expression1, value-expression2 )
Returns the angle in radians of the point (value-expression1, value-expression2) to the y-axis.

Examples

Input Output
1,1 7.85398163397448e-01
-1,0 -1.57079632679490e+00
90.6787,15.1873 1.40485177571872e+00

CREATE TABLE num_table (col1 DEC(20,7), col2 DEC(20,7), col3 FLOAT);
INSERT INTO num_table VALUES (1, 1, );
1 row affected
INSERT INTO num_table VALUES (-1, 0, );
1 row affected
INSERT INTO num_table VALUES (90.6787, 15.1873, );
1 row affected
UPDATE num_table SET col3 = ATAN2 (col1, col2);
SELECT * FROM num_table;
3 rows selected
COL1                   COL2                   COL3
---------------------- ---------------------- ----------------------
             1.0000000              1.0000000   7.85398163397448e-01
            -1.0000000              0.0000000  -1.57079632679490e+00
            90.6787000             15.1873000   1.40485177571872e+00


ATAN2D ( value-expression1, value-expression2 )
Same as the ATAN2 function, but returns the value in degrees rather than radians.

Examples

Input Output
1,1 45
-1,0 -90
90.6787,15.1873 80.4920775901419

CREATE TABLE num_table (col1 DEC(20,7), col2 DEC(20,7), col3 FLOAT);
INSERT INTO num_table VALUES (1, 1, );
1 row affected
INSERT INTO num_table VALUES (-1, 0, );
1 row affected
INSERT INTO num_table VALUES (90.6787, 15.1873, );
1 row affected
UPDATE num_table SET col3 = ATAN2D (col1, col2);
SELECT * FROM num_table;
3 rows selected
COL1                   COL2                   COL3
---------------------- ---------------------- ----------------------
             1.0000000              1.0000000   4.50000000000000e+01
            -1.0000000              0.0000000  -9.00000000000000e+01
            90.6787000             15.1873000   8.04920775901419e+01


ATAND ( value-expression )
Same as the ATAN function, but returns the value in degrees rather than radians.

Examples

Input Output
2.5e-2 1.43209618416465e+00
2.5 6.81985905136482e+01
-20 -8.71375947738883e+01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (2.5e-2, 2.5, -20);
1 row affected
SELECT ATAND(col1), ATAND(col2), ATAND(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  1.43209618416465e+00   6.81985905136482e+01  -8.71375947738883e+01


ATANH ( value-expression )
Returns the hyperbolic arctangent (inverse tangent) of the input value. If the argument is null, this function will return a null value.

Examples

Input Output
2.49947936189202e-02 2.50000006509062e-02
0 0.00000000000000e+00
-0.0003648 -3.64800016182413e-04

CREATE TABLE num_table (col1 FLOAT, col2 SMALLINT, col3 DEC(10,8));
INSERT INTO num_table VALUES (2.49947936189202e-02, 0,
-0.0003648);
1 row affected
SELECT ATANH(col1), ATANH(col2), ATANH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  2.50000006509062e-02   0.00000000000000e+00  -3.64800016182413e-04


CEILING ( value-expression )
Returns the smallest integer that is greater than or equal to the input expression.

Example

Input Output
9.3 10


CREATE TABLE num_table (col1 DEC(5,2));
INSERT INTO num_table VALUES (9.3);
1 row affected
SELECT CEILING(col1) FROM num_table;
1 row selected
1
----------------------
  1.00000000000000e+01


COS ( value-expression )
Returns the cosine of the specified angle in radians. If the argument is null, this function will return a null value.

Examples

Input Output
pi()/2 -3.38076867830836e-15
-1.0 5.40302305868140e-01
0 1.00000000000000e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (pi()/2, -1.0, 0);
1 row affected
SELECT COS(col1), COS(col2), COS(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -3.38076867830836e-15   5.40302305868140e-01   1.00000000000000e+00


COSD ( value-expression )
Same as the COS function, but the input is in degrees rather than radians.

Examples

Input Output
pi()/2 9.99624216859482e-01
-1.0 9.99847695156391e-01
0 1.00000000000000e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (pi()/2, -1.0, 0);
1 row affected
SELECT COSD(col1), COSD(col2), COSD(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  9.99624216859482e-01   9.99847695156391e-01   1.00000000000000e+00


COSH ( value-expression )
Returns the hyperbolic cosine of the input value.

Examples

Input Output
pi()/2 2.50917847865807e+00
-1.0 1.54308063481524e+00
0 1.00000000000000e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (pi()/2, -1.0, 0);
1 row affected
SELECT COSH(col1), COSH(col2), COSH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  2.50917847865807e+00   1.54308063481524e+00   1.00000000000000e+00


EXP ( value-expression )
Returns a FLOAT value of the logarithmic constant e (2.718281828) raised to the power of the parameter.

Example

Input Output
100 2.68811714181614e+43


CREATE TABLE num_table (col1 SMALLINT);
INSERT INTO num_table VALUES (100);
1 row affected
SELECT EXP(col1) FROM num_table;
1 row selected
1
----------------------
  2.68811714181614e+43


FACTORIAL ( value-expression )
Returns the factorial of the input value, an integer from 0 to 82.

Example

Input Output
5 120
0 1
82 4.7536433370128417e122


CREATE TABLE num_table (col1 INT, col2 INT, col3 INT);
INSERT INTO num_table VALUES (5, 0, 82);
1 row affected
SELECT FACTORIAL(col1), FACTORIAL(col2), FACTORIAL(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  1.20000000000000e+02   1.00000000000000e+00  4.75364333701284e+122


FLOOR ( value-expression )
Returns the largest integer that is less than or equal to the input expression.

Example

Input Output
9.3 9


CREATE TABLE num_table (col1 DEC(5,2));
INSERT INTO num_table VALUES (9.3);
1 row affected
SELECT FLOOR(col1) FROM num_table;
1 row selected
1
----------------------
  9.00000000000000e+00


GCD ( integer1, integer2 )
Returns the greatest common divisor (denominator) of the two specified integers.

Examples

Input Output
18, 84 6
-12, 15 3
17, 23 1

CREATE TABLE num_table (col1 INT, col2 INT);
INSERT INTO num_table values (18, 84);
1 row affected
INSERT INTO num_table values (-12, 15);
1 row affected
INSERT INTO num_table values (17, 23);
1 row affected
SELECT GCD(col1, col2) AS GCD FROM num_table;
3 rows selected
GCD
-----------
          6
          3
          1


LCM ( integer1, integer2 )
Returns the least (lowest) common multiple of the two specified integers.

Examples

Input Output
5,6 30
24,16 48
-1,99 99

CREATE TABLE num_table (col1 INT, col2 INT);
INSERT INTO num_table values (5, 6);
1 row affected
INSERT INTO num_table values (24, 16);
1 row affected
INSERT INTO num_table values (-1, 99);
1 row affected
SELECT LCM(col1, col2) AS LCM FROM num_table;
3 rows selected
LCM
-----------
         30
         48
         99


LN ( value-expression )
Returns the natural logarithm of the input expression. If the input expression evaluates to a negative number or 0, an error message is returned.

Example

Input Output
120 4.78749174278205e+00


CREATE TABLE num_table (col1 SMALLINT);
INSERT INTO num_table VALUES (120);
1 row affected
SELECT LN(col1) FROM num_table;
1 row selected
1
----------------------
  4.78749174278205e+00


LOG ( [ b, ] n )
Returns the logarithm of n base b, where b and n are positive numeric value expressions. If the input expression evaluates to a negative number or 0, an error message is returned.

If the b argument is omitted, the natural logarithm of n is returned (the equivalent of LN(n)).

Example

Input Output
8, 16 1.33333333333333e+00


CREATE TABLE num_table (col1 SMALLINT, col2 SMALLINT)
INSERT INTO num_table VALUES (8, 16);
1 row affected
SELECT LOG(8, 16) AS LOG8 FROM num_table;
1 row selected
LOG8
----------------------
  1.33333333333333e+00


LOG10 ( value-expression )
Returns the base 10 logarithm of the input expression. If the input expression evaluates to a negative number or 0, an error message is returned.

Example

Input Output
2 3.01029995663981e-01


CREATE TABLE num_table (col1 SMALLINT);
INSERT INTO num_table VALUES (2);
1 row affected
SELECT LOG10(col1) FROM num_table;
1 row selected
1
----------------------
  3.01029995663981e-01


MOD ( value-expression1, value-expression2 )
Returns the remainder of value-expression1 divided by value-expression2. Both arguments must be integral; decimal values are not supported. The return value of the modulus function is negative only if value-expression1 is negative. If value-expression2 is 0, the function returns value-expression1. If either of the arguments is a null value, the result is a null value.

Examples

Input Output
8, 5 3
127,62 3
-4,2 0
-4,3 -1
NULL,1 NULL


CREATE TABLE num_table (exp1 SMALLINT, exp2 SMALLINT, result SMALLINT);
INSERT INTO num_table VALUES (8, 5, );
1 row affected
INSERT INTO num_table VALUES (127, 62, );
1 row affected
INSERT INTO num_table VALUES (-4, 2, );
1 row affected
INSERT INTO num_table VALUES (-4, 3, );
1 row affected
INSERT INTO num_table VALUES ( , 1, );
1 row affected
UPDATE num_table SET result = MOD(exp1, exp2);
5 rows affected
.NULLS *****
SELECT * FROM num_table;
5 rows selected
EXP1   EXP2   RESULT
------ ------ ------
     8      5      3
   127     62      3
    -4      2      0
    -4      3     -1
*****       1 *****


NROOT ( n, m )
Returns the nth root of a number m, where n is a non-zero integer and m is a numeric value expression.

Example

Input Output
3, 8 2
-1, 8 0.125
5, 87 2.44288965573739


CREATE TABLE num_table (col1 INT, col2 FLOAT);
INSERT INTO num_table VALUES (3,8);
1 row affected
INSERT INTO num_table VALUES (-1, 8);
1 row affected
INSERT INTO num_table VALUES (5, 87);
1 row affected
SELECT NROOT(col1, col2) AS nROOT FROM num_table;
3 rows selected
nROOT
----------------------
  2.00000000000000e+00
  1.25000000000000e-01
  2.44288965573739e+00


PI ( )
Returns the constant pi (that is, 3.14159265...) as a FLOAT value.

Example

CREATE TABLE num_table (col1 SMALLINT);
INSERT INTO num_table VALUES (2);
1 row affected
SELECT PI(), PI()*col1 FROM num_table;
1 row selected
1                      2
---------------------- ----------------------
  3.14159265358979e+00   6.28318530717958e+00


POWER ( value-expression1, value-expression2 )
Returns a value that is calculated as value-expression1 raised to the power of value-expression2. If both input values are integers, then the result is INTEGER; otherwise the result is a FLOAT value.

Examples

Input Output
4, 8 65536
4.0, 8 6.55360000000000e+04
4, 0.5 2.00000000000000e+00
4, -2.0 6.25000000000000e-02


CREATE TABLE num_table (col1 SMALLINT, col2 SMALLINT);
INSERT INTO num_table VALUES (4, 8);
1 row affected
SELECT POWER(col1, col2) FROM num_table;
1 row selected
1
-----------
      65536
SELECT POWER(4.0, col2) FROM num_table;
1 row selected
1
----------------------
  6.55360000000000e+04
SELECT POWER(col1, 0.5) FROM num_table;
1 row selected
1
----------------------
2.00000000000000e+00
SELECT POWER(col1, -2.0) FROM num_table;
1 row selected
1
----------------------
  6.25000000000000e-02


ROUND ( value-expression1, value-expression2 )
Returns the value produced by rounding value-expression1 to value-expression2 digits to the right of the decimal point. A negative number of digits indicates rounding to the left of the decimal point. Note that value-expression1 can be any numeric value, but value-expression2 must be an integer value.

Example

Input Output
234.56789, 2 234.57
234.56789, -2 200


CREATE TABLE num_table (col1 DEC(10, 2));
INSERT INTO num_table VALUES (234.56789);
1 row affected
SELECT ROUND(col1, 2) FROM num_table;
1 row selected
1
-------------
       234.57
SELECT ROUND(col1, -2) FROM num_table;
1 row selected
1
-------------
       200.00


SIGN ( value-expression )
Returns a value that indicates the sign of the input expression. The possible return values are the following:

If the input value is an INTEGER, then the return value is INTEGER as well. Similarly, if the input is SMALLINT, the output is SMALLINT. Otherwise, a FLOAT value is returned.

Examples

Input Output
173 1
-173 -1
-173.34 -1.00000000000000e+00
173.34 1.00000000000000e+00
0 0


CREATE TABLE num_table (int_col INT, float_col FLOAT);
INSERT INTO num_table VALUES (173, 173.34);
1 row affected
INSERT INTO num_table VALUES (-173, -173.34);
1 row affected
INSERT INTO num_table VALUES (0, );
1 row affected
SELECT int_col, SIGN(int_col) AS result FROM num_table;
3 rows selected
int_col     result
----------- -----------
        173           1
       -173          -1
          0           0
SELECT float_col, SIGN(float_col) AS result FROM num_table WHERE float_col IS NOT NULL;
2 rows selected
float_col              result
---------------------- ----------------------
  1.73340000000000e+02   1.00000000000000e+00
 -1.73340000000000e+02  -1.00000000000000e+00

 

SIN ( value-expression )
Returns the sine of the specified angle in radians. If the argument is null, this function will return a null value.

Examples

Input Output
pi() 3.23846264338328e-15
-1.5707963 -1.00000000000000e+00
3 1.41120008059867e-01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,7), col3 SMALLINT);
INSERT INTO num_table VALUES (pi(), -1.5707963, 3);
1 row affected
SELECT SIN(col1), SIN(col2), SIN(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  3.23846264338328e-15  -1.00000000000000e+00   1.41120008059867e-01


SIND ( value-expression )
Same as the SIN function, but the input is in degrees rather than radians.

Examples

Input Output
pi() 5.48036651487895e-02
-1.5707963 -2.74121331245609e-02
3 5.23359562429438e-02

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,7), col3 SMALLINT);
INSERT INTO num_table VALUES (pi(), -1.5707963, 3);
1 row affected
SELECT SIND(col1), SIND(col2), SIND(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  5.48036651487895e-02  -2.74121331245609e-02   5.23359562429438e-02


SINH ( value-expression )
Returns the hyperbolic sine of the input value.

Examples

Input Output
pi() 1.15487393572577e+01
-1.5707963 -2.30129883507412e+00
3 1.00178749274099e+01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,7), col3 SMALLINT);
INSERT INTO num_table VALUES (pi(), -1.5707963, 3);
1 row affected
SELECT SINH(col1), SINH(col2), SINH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
  1.15487393572577e+01  -2.30129883507412e+00   1.00178749274099e+01


SQRT ( value-expression )
Returns a FLOAT value that is the square root of the input expression.

Examples

Input Output
81 9.00000000000000e+00
4678 6.83959063102464e+01


CREATE TABLE num_table (col1 INT);
INSERT INTO num_table VALUES (81);
1 row affected
INSERT INTO num_table VALUES (4678);
1 row affected
SELECT col1, SQRT(col1) AS result FROM num_table;
2 rows selected
col1        result
----------- ----------------------
         81   9.00000000000000e+00
       4678   6.83959063102464e+01


TAN ( value-expression )
Returns the tangent of the specified angle in radians. If the argument is null, this function will return a null value.

Examples

Input Output
-1.4578e-10 -1.45780000000000e-10
-1.561 -1.02075811821295e+02
2 -2.18503986326152e+00

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-1.4578e-10, -1.561, 2);
1 row affected
SELECT TAN(col1), TAN(col2), TAN(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -1.45780000000000e-10  -1.02075811821295e+02  -2.18503986326152e+00


TAND ( value-expression )
Same as the TAN function, but the input is in degrees rather than radians.

Examples

Input Output
-1.4578e-10 -2.54434098355733e-12
-1.561 -2.72513325516249e-02
2 3.49207694917477e-02

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-1.4578e-10, -1.561, 2);
1 row affected
SELECT TAND(col1), TAND(col2), TAND(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -2.54434098355733e-12  -2.72513325516249e-02   3.49207694917477e-02


TANH ( value-expression )
Returns the hyperbolic tangent of the input value.

Examples

Input Output
-1.4578e-10 -1.45780000000000e-10
-1.561 -9.15582313482777e-01
2 9.64027580075817e-01

CREATE TABLE num_table (col1 FLOAT, col2 DEC(10,3), col3 SMALLINT);
INSERT INTO num_table VALUES (-1.4578e-10, -1.561, 2);
1 row affected
SELECT TANH(col1), TANH(col2), TANH(col3) FROM num_table;
1 row selected
1                      2                      3
---------------------- ---------------------- ----------------------
 -1.45780000000000e-10  -9.15582313482777e-01   9.64027580075817e-01

 


Previous Topic:
String Functions
Chapter Index
 Next Topic:
Date/Time Functions