Subqueries in Expressions
1.7 SQL Functions
1.7.4 Number Functions
Number functions take numeric values, or strings with numeric characters, as inputs and return numeric values.
When strings with numeric characters are provided as inputs implicit conversion from a string to a number is performed automatically before results are computed.
● ABS
● SIGN
● SIN
● SINH
● SQRT
● TAN
● TANH
● UMINUS
1.7.4.1 ABS
Syntax
ABS (n)
Description
Returns the absolute value of the numeric argument n.
Example
SELECT ABS (-1) "absolute" FROM DUMMY;
absolute 1
1.7.4.2 ACOS
Syntax
ACOS (n)
Description
Returns the arc-cosine, in radians, of the numeric argument n between -1 and 1.
Example
SELECT ACOS (0.5) "acos" FROM DUMMY;
acos
1.0471975511965979
1.7.4.3 ASIN
Syntax
ASIN (n)
Description
Returns the arc-sine, in radians, of the numeric argument n between -1 and 1.
Example
SELECT ASIN (0.5) "asin" FROM DUMMY;
asin
0.5235987755982989
1.7.4.4 ATAN
Syntax
ATAN (n)
Description
Returns the arc-tangent, in radians, of the numeric argument n. The range of n is unlimited.
Example
SELECT ATAN (0.5) "atan" FROM DUMMY;
atan
0.4636476090008061
1.7.4.5 ATAN2
Syntax
ATAN2 (n, m)
Description
Returns the arc-tangent, in radians, of the ratio of two numbers n and m. This produces the same result as ATAN(n/m).
Example
SELECT ATAN2 (1.0, 2.0) "atan2" FROM DUMMY;
atan2
0.4636476090008061
1.7.4.6 BINTOHEX
Syntax
BINTOHEX (expression)
Description
Converts a binary value to a hexadecimal value.
Example
SELECT BINTOHEX('AB') "bintohex" FROM DUMMY;
bintohex 4142
1.7.4.7 BITAND
Syntax
BITAND (n, m)
Description
Performs an AND operation on the bits of the arguments n and m. Both n and m must be non-negative integers.
The BITAND function returns a result with BIGINT type.
Example
SELECT BITAND (255, 123) "bitand" FROM DUMMY;
bitand 123
1.7.4.8 CEIL
Syntax
CEIL (n)
Description
Returns the first integer that is greater or equal to the value n.
Example
SELECT CEIL (14.5) "ceiling" FROM DUMMY;
ceiling 15
1.7.4.9 COS
Syntax
COS (n)
Description
Returns the cosine of the angle, in radians, of the argument n.
Example
SELECT COS (0.0) "cos" FROM DUMMY;
cos 1.0
1.7.4.10 COSH
Syntax
COSH (n)
Description
Computes the hyperbolic cosine of the argument n.
Example
SELECT COSH (0.5) "cosh" FROM DUMMY;
cosh
1.1276259652063807
1.7.4.11 COT
Syntax
COT (n)
Description
Computes the cotangent of a number n, where the argument is an angle expressed in radians.
Example
SELECT COT (40) "cot" FROM DUMMY;
cot
-0.8950829176379128
1.7.4.12 EXP
Syntax
EXP (n)
Description
Returns the result of the base of natural logarithms e raised to the power of the argument n.
Example
SELECT EXP (1.0) "exp" FROM DUMMY;
exp 2.718281828459045
1.7.4.13 FLOOR
Syntax
FLOOR (n)
Description
Returns the largest integer not greater than the numeric argument n.
Example
SELECT FLOOR (14.5) "floor" FROM DUMMY;
floor 14
1.7.4.14 HEXTOBIN
Syntax
HEXTOBIN (value)
Description
Converts a hexadecimal value to a binary value.
Example
SELECT HEXTOBIN ('1a') "hextobin" FROM DUMMY;
hextobin 1A
1.7.4.15 LN
Syntax
LN (n)
Description
Returns the natural logarithm of the argument n.
Example
SELECT LN (9) "ln" FROM DUMMY;
ln 2.1972245773362196
1.7.4.16 LOG
Syntax
LOG (b, n)
Description
Returns 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.
Example
SELECT LOG (10, 2) "log" FROM DUMMY;
log 0.30102999566398114
1.7.4.17 MOD
Syntax
MOD (n, d)
Description
Returns the remainder of a number n divided by a divisor d.
When n is negative this function acts differently to the standard computational modulo operation.
The following explains example of what the MOD function returns as the result.
● If d is zero, then n is returned.
● If n is greater than 0 and n is less than d, then n is returned.
● If n is less than 0 and n is greater than d, then n is returned.
● In other case that those mentioned above, remainder of the absolute value of n divided by the absolute value of d is used to calculate remainder. If n is less than 0, then the returned remainder from MOD is a negative number, and if n is greater than 0, then the returned remainder from MOD is a positive number.
Example
SELECT MOD (15, 4) "modulus" FROM DUMMY;
modulus 3
SELECT MOD (-15, 4) "modulus" FROM DUMMY;
modulus -3
1.7.4.18 POWER
Syntax
POWER (b, e)
Description
Calculates the base number b raised to the power of an exponent e.
Example
SELECT POWER (2, 10) "power" FROM DUMMY;
power 1024.0
1.7.4.19 RAND
Syntax
DOUBLE RAND()
Description
Returns a pseudo-random value in the range of [0, 1.0). Its return value type is DOUBLE.
Example
SELECT RAND() FROM DUMMY;
RAND
3.907985046680551e-14
1.7.4.20 ROUND
Syntax
ROUND (n [, pos])
ROUND (n, pos [, rounding_mode])
Description
Rounds argument n to the specified pos amount of places after the decimal point.
The rounding_mode defines how the rounding should be carried out. The options for this parameter are as follows:
Rounding Mode Rounding Rule
Rounding Mode Rounding Rule
ROUND_HALF_UP The value is rounded up to the next round figure. If the value falls precisely halfway between two rounded values it is rounded up away from zero (as is done in commercial rounding).
ROUND_HALF_DOWN The value is rounded down to the next round figure. If the value falls precisely halfway between two round values it is rounded down towards zero.
ROUND_HALF_EVEN The value is rounded to the next round figure. If the value falls precisely halfway between two rounded values it is rounded to the value whose last decimal place is an even number.
ROUND_UP The value is always rounded away from zero, to the
larger round figure.
ROUND_DOWN The value is always rounded towards zero, to the
smaller round figure.
ROUND_CEILING The value is always rounded in a positive direction, to the larger value.
ROUND_FLOOR The value is always rounded in a negative direction, to
the smaller value.
By default, ROUND function uses ROUND_HALF_UP.
Note
When using ROUND with floating point types, REAL and DOUBLE, the precision of the numeric representation can affect the result that will be obtained. In this case the actual number of digits after the decimal point will be influenced by the precision of the type used. For example:
select ROUND(TO_REAL(399.71429443359375),2) from DUMMY;
The actual result of this statement will be 399.7099914550781 and not 399.71.
Examples
SELECT ROUND (16.16, 1) "round" FROM DUMMY;
round 16.2
SELECT ROUND (16.16, -1) "round" FROM DUMMY;
round 20
SELECT ROUND( 438.75, 1, ROUND_HALF_UP) "round" FROM DUMMY;
round 438.8
SELECT ROUND( 438.75, 1, ROUND_HALF_DOWN) "round" FROM DUMMY;
round 438.7
SELECT ROUND( 438.75, 1, ROUND_HALF_EVEN) "round" FROM DUMMY;
round 438.8
SELECT ROUND( 438.71, 1, ROUND_UP) "round" FROM DUMMY;
round 438.8
SELECT ROUND( 438.79, 1, ROUND_DOWN) "round" FROM DUMMY;
round 438.7
SELECT ROUND( 438.75, 1, ROUND_CEILING) "round" FROM DUMMY;
round 438.8
SELECT ROUND( 438.75, 1, ROUND_FLOOR) "round" FROM DUMMY;
round 438.7
1.7.4.21 SIGN
Syntax
SIGN (n)
Description
Returns 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.
Example
SELECT SIGN (-15) "sign" FROM DUMMY;
sign -1
1.7.4.22 SIN
Syntax
SIN (n)
Description
Returns the sine of n, where the argument is an angle expressed in radians.
Example
SELECT SIN ( 3.141592653589793/2) "sine" FROM DUMMY;
sine 1.0
1.7.4.23 SINH
Syntax
SINH (n)
Description
Returns the hyperbolic sine of n, where the argument is an angle expressed in radians.
Example
SELECT SINH (0.0) "sinh" FROM DUMMY;
sinh 0.0
1.7.4.24 SQRT
Syntax
SQRT (n)
Description
Returns the square root of the argument n.
Example
SELECT SQRT (2) "sqrt" FROM DUMMY;
sqrt
1.4142135623730951
1.7.4.25 TAN
Syntax
TAN (n)
Description
Returns the tangent of n, where the argument is an angle expressed in radians.
Example
SELECT TAN (0.0) "tan" FROM DUMMY;
tan 0.0
1.7.4.26 TANH
Syntax
TANH (n)
Description
Returns the hyperbolic tangent of the numeric argument n.
Example
SELECT TANH (1.0) "tanh" FROM DUMMY;
tanh
0.7615941559557649
1.7.4.27 UMINUS
Syntax
UMINUS (n)
Description
Returns the negated value of the numeric argument n.
Example
SELECT UMINUS(-765) "uminus" FROM DUMMY;
uminus 765