NUMERIC FUNCTIONS

ABS: This function is used to return the absolute value of the numeric argument n.

Syntax: ABS (n)
Example: SELECT ABS(-678) AS “ABSOLUTE VALUE” FROM DUMMY
Result: 678 (Absolute values for -678)

ACOS: This function is used to return the arc-cosine, in radians, of the numeric argument n between -1 and 1.

Syntax: ACOS (n)
Example: SELECT ACOS (.3) “acos” FROM DUMMY;
Result: acos: 1.2661036727794992

ASIN: This function is used to return the arc-sine, in radians, of the numeric argument n between -1 and 1.
Syntax: ASIN (n)
Example: SELECT ASIN (0.3) “asin” FROM DUMMY;
Result: asin: 0.3046926540153975

ATAN: This function is used to return the arc-tangent, in radians, of the numeric argument n. The range of n is unlimited.
Syntax: ATAN (n)
Example: SELECT ATAN (0.3) “atan” FROM DUMMY;
Result: atan: 0.2914567944778671

ATAN2: This function is used to return the arc-tangent, in radians, of the ratio of two numbers n and m. This produces the same result as ATAN(n/m).
Syntax: ATAN2 (n, m)
Example: SELECT ATAN2 (0.5, 1.0) “atan2” FROM DUMMY;
Result: atan2: 0.4636476090008061

BINTOHEX: This function is used to convert a binary value to a hexadecimal value.
Syntax: BINTOHEX (expression)
Example: SELECT BINTOHEX(‘A’) “bintohex” FROM DUMMY;
Result: bintohex: 41

BITAND: This function is used to perform an AND operation on the bits of the arguments n and m. Both n and m must be non-negative integers or varbinary. The BITAND function return a result along argument’s type.
Syntax: BITAND (n, m)
Example: SELECT BITAND (20, 40) “bitand” FROM DUMMY;
Result: bitand: 0

BITCOUNT: This function is used to count the number of set bits of the argument <expression>. <expression> must be an integer or a varbinary. The BITCOUNT function return a integer type.
Syntax: BITCOUNT (<expression>)
Example: SELECT BITCOUNT (20) “bitcount” FROM DUMMY;
Result: bitcount: 2

BITNOT: This function is used to perform a bitwise NOT operation on the bits of the argument <expression>. <expression>must be an integer. The BITNOT function returns a result along argument’s type.
Syntax: BITNOT (expression)
Example: SELECT BITNOT (20) “bitnot” FROM DUMMY;
Result: bitnot: -20

BITOR: This function performs an OR operation on the bits of the arguments <expression1> and <expression2>. Both <expression1> and <expression2> must be non-negative integers or varbinary. The BITOR function returns a result along argument’s type.
Syntax: BITOR (<expression1>, <expression2>)
Example: SELECT BITOR (20, 40) “bitor” FROM DUMMY;
Result: bitor: 60

BITSET: This function is used to set <num_to_set> bits to 1 in <target_num> from the <start_bit> position.
Syntax: BITSET (<target_num>, <start_bit>, <num_to_set>)
Example: SELECT BITSET (‘abcd’, 1, 2) “bitset” FROM DUMMY;
Result: bitset: EBCD

BITUNSET: This function is used to set <num_to_unset> bits to 0 in <target_num> from the <start_bit> position.
Syntax: BITUNSET (<target_num>, <start_bit>, <num_to_unset>)
Example: SELECT BITUNSET (‘abcd’, 1, 2) “bitunset” FROM DUMMY;
Result: bitunset: 2BCD

BITXOR: This function is used to perform an XOR operation on the bits of the arguments <expression1> and <expression2>. Both <expression1> and <expression2> must be non-negative integers or varbinary. The BITXOR function returns a result along argument’s type.
Syntax: BITXOR (expression1, expression2)
Example: SELECT BITXOR (20,40) “bitxor” FROM DUMMY;
Result: bitxor: 60

CEIL: This function is used to return the first integer that is greater or equal to the value n.
Syntax: CEIL (n)
Example: SELECT CEIL (2.1) “ceiling” FROM DUMMY;
Result: ceiling: 3

COS: This function is used to return the cosine of the angle, in radians, of the argument n.
Syntax: COS (n)
Example: SELECT COS (0.5) “cos” FROM DUMMY;
Result: cos: 0.8775825618903728

COSH: This function is used to compute the hyperbolic cosine of the argument n.
Syntax: COSH (n)
Example: SELECT COSH (0.5) “cosh” FROM DUMMY;
Result: cosh: 1.1276259652063807

COT: This function is used to compute the cotangent of a number n, where the argument is an angle expressed in radians.
Syntax: COT (n)
Example: SELECT  COT (0.5) “cot” FROM DUMMY;
Result: cot: 1.830487721712452

EXP: This function is used to return the result of the base of natural logarithms e raised to the power of the argument n.
Syntax: EXP (n)
Example: SELECT EXP (2.0) “exp” FROM DUMMY;
Result: exp: 7.38905609893065

FLOOR: This function is used to return the largest integer not greater than the numeric argument n.
Syntax: FLOOR (n)
Example: SELECT FLOOR (2.1) “floor” FROM DUMMY;
Result: floor: 2

HEXTOBIN: This function is used to convert a hexadecimal value to a binary value.
Syntax: HEXTOBIN (value)
Example: SELECT HEXTOBIN (‘B’) “HEXTOBIN” FROM DUMMY;
Result: : 0B

LN: This function is used to return the natural logarithm of the argument n.
Syntax: LN (n)
Example: SELECT LN (2) “LN” FROM DUMMY;
Result: LN: 0.6931471805599453

LOG: This function is used to return the natural logarithm of a number n base b. Base b must be a positive value greater than 1 and n must be any positive value.
Syntax: LOG (b, n)
Example: SELECT LOG (15, 3) “log” FROM DUMMY;
Result: log: 0.40568387108221293

MOD: This function is used to return the remainder of a number n divided by a divisor d.
Syntax: MOD (n, d)
Example: SELECT MOD (35, 8) “Modulus” FROM DUMMY;
Result: Modulus: 3

POWER: This function is used to calculate the base number b raised to the power of an exponent e.
Syntax: POWER (b, e)
Example: SELECT POWER (5, 6) “Power” FROM DUMMY;
Result: Power: 15625

RAND: This function is used to return a pseudo-random value in the range of 0 to 1.0. Its return value type is DOUBLE.
Syntax: DOUBLE RAND()
Example: SELECT RAND() FROM DUMMY;
Result: RAND: 0.0009853946746503084

ROUND: This function is used to round argument <n> to the specified <pos> amount of places after the decimal point. The <rounding_mode> defines how the rounding should be carried out.
Syntax: ROUND (<n> [, <pos>])
ROUND (<n>, <pos> [, <rounding_mode>])
Example: SELECT ROUND (5.23, -1) “round” FROM DUMMY;
SELECT ROUND( 5.23, 1, ROUND_HALF_DOWN) “round” FROM DUMMY;
Result:  round: 10
round: 5.2

SIGN: This function is used to return the sign (positive or negative) of the numeric argument n. Returns 1 if n is a positive value,-1 if n is a negative value, and 0 if n is equal to zero.
Syntax: SIGN (n)
Example: SELECT SIGN (-5) “sign” FROM DUMMY;
Result: sign: -1

SIN: This function is used to return the sine of n, where the argument is an angle expressed in radians.

Syntax: SIN (n)
Example: SELECT SIN (0.5) “sine” FROM DUMMY;
Result: sine: 0.479425538604203

SINH: This function is used to return the hyperbolic sine of n, where the argument is an angle expressed in radians.
Syntax: SINH (n)
Example: SELECT SINH (0.5) “sinh” FROM DUMMY;
Result: sinh: 0.5210953054937474

SQRT: This function is used to return the square root of the argument n.
Syntax: SQRT (n)
Example: SELECT SQRT (117) “sqrt” FROM DUMMY;
Result: sqrt: 10.816653826391969

TAN: This function is used to return the tangent of n, where the argument is an angle expressed in radians.
Syntax: TAN (n)
Example: SELECT TAN (0.5) “tan” FROM DUMMY;
Result: tan: 0.5463024898437905

TANH: This function is used to return the hyperbolic tangent of the numeric argument n.
Syntax: TANH (n)
Example: SELECT TANH (0.5) “tanh” FROM DUMMY;
Result: tanh: 0.46211715726000974

UMINUS: This function is used to return the negated value of the numeric argument n.
Syntax: UMINUS (n)
Example: SELECT  UMINUS(444) “uminus” FROM DUMMY;
Result: -444