Numeric Functions
Numeric Functions#
Numeric functions accept numeric input values as parameters, perform operations on them, and return numeric values.
ABS#
Syntax#
ABS (number)
Description#
ABS returns the absolute value of the input number.
Examples#
<Query> Display the absolute values for three numeric values.
SELECT ABS(-1), ABS(0.0), ABS(1) FROM DUAL;
ABS(-1) ABS(0.0) ABS(1)
----------------------------------------
1 0 1
1 row selected.
Calculate the difference between the most expensive item and the most inexpensive item in the goods table.
SELECT ABS(MIN(price) - MAX(price)) absolute_value FROM goods;
ABSOLUTE_VALUE
-----------------
99033.01
1 row selected.
ACOS#
Syntax#
ACOS (number)
Description#
ACOS returns the arccosine of the input argument, which must be within the range from -1 to 1 inclusive. If the input value does not fall within this range, 0.000000 is returned. The function returns a DOUBLE type value in radians within the range from 0 to π(pi). (1 radian = 180º/pi)
Example#
<Query>
SELECT ACOS(.3) Arc_Cosine FROM DUAL;
ARC_COSINE
--------------
1.2661036727795
1 row selected.
ASIN#
Syntax#
ASIN (number)
Description#
ASIN returns the arcsine of the input argument, which must be within the range from -1 to 1 inclusive. If the input value does not fall within this range, 0.000000 is returned. The function returns a DOUBLE type numeric value in radians within the range from -π/2 to π/2 inclusive.
Example#
<Query>
SELECT ASIN(.3) Arc_Sine FROM DUAL;
ARC_SINE
--------------
0.304692654015398
1 row selected.
ATAN#
Syntax#
ATAN (number)
Description#
ATAN returns the arc tangent of the input argument, which can be any real number. The function returns a DOUBLE type numeric value in radians within the range from -π/2 to π/2 inclusive.
Example#
<Query>
SELECT ATAN(.3) Arc_Tangent FROM DUAL;
ARC_TANGENT
--------------
0.291456794477867
1 row selected.
ATAN2#
Example#
ATAN2 (n, m)
Description#
ATAN2 returns the arc tangent of two input arguments, which can be any real number. The function returns a DOUBLE type numeric value in radians within the range from -π (exclusive) to π (inclusive).
Example#
<Query>
SELECT ATAN2(.3, .2) Arc_Tangent2 FROM DUAL;
ARC_TANGENT2
---------------
0.982793723247329
1 row selected.
CEIL#
Syntax#
CEIL (number)
Description#
CEIL returns the smallest integer that is greater than or equal to n.
Example#
<Query> Return the smallest integer that is greater than or equal to each of the input numbers.
SELECT CEIL(99.9), CEIL(-99.9) FROM DUAL;
CEIL(99.9) CEIL(-99.9)
---------------------------
100 -99
1 row selected.
<Query> Calculate the difference between the most expensive item and the most inexpensive item in the table goods, and return the smallest integer that is greater than or equal to this difference.
SELECT CEIL(ABS (MIN(price) - MAX(price))) Smallest_int FROM goods;
SMALLEST_INT
---------------
99034
1 row selected.
COS#
Syntax#
COS (number)
Description#
COS is a trigonometric function that returns the cosine, expressed in radians, of an input floating-point number, also expressed in radians. The return type is DOUBLE.
Example#
<Query>
SELECT COS(180 * 3.14159265359/180) Cos_of_180_degrees FROM DUAL;
COS_OF_180_DEGREES
---------------------
-1
1 row selected.
COSH#
Syntax#
COSH (number)
Description#
COSH returns the hyperbolic cosine of the input number. The return type is DOUBLE.
COSH(n) = ( e n + e -n )/2
Example#
<Query>
SELECT COSH(0) FROM DUAL;
COSH(0)
--------------
1
1 row selected.
EXP#
Syntax#
EXP (n)
Description#
EXP returns the mathematical constant e to the power of the input number. (e = 2.71828183…) The return type is DOUBLE.
Example#
<Query>
SELECT EXP(2.4) FROM DUAL;
EXP(2.4)
--------------
11.0231763806416
1 row selected.
FLOOR#
Syntax#
FLOOR (number)
Description#
FLOOR returns the greatest integer that is less than or equal to n.
Examples#
<Query> Return the greatest integer that is less than or equal to each of the input numbers.
SELECT FLOOR(99.9), FLOOR(-99.9) FROM DUAL;
FLOOR(99.9) FLOOR(-99.9)
----------------------------
99 -100
1 row selected.
<Query> Calculate the difference between the most expensive item and the most inexpensive item in the table goods, and return the greatest integer that is less than or equal to this difference.
SELECT FLOOR(ABS(MIN(price) - MAX(price))) Largest_int FROM goods;
LARGEST_INT
--------------
99033
1 row selected.
ISNUMERIC, IS_NUMERIC#
Syntax#
ISNUMERIC (expr)
Description#
This function evaluates whether or not the input expression is a valid numeric data type. If it is valid, 1 is returned; if it is invalid, 0 is returned.
Example#
<Query> Evaluate whether or not the input data '1.4' is a valid numeric data type.
SELECT ISNUMERIC('1.4') FROM DUAL;
ISNUMERIC('1.4')
-------------------
1
1 row selected.
LN#
Syntax#
LN (n)
Description#
LN returns the natural logarithm of n, which must be greater than 0.
Example#
<Query>
SELECT LN(2.4) FROM DUAL;
LN(2.4)
--------------
0.8754687373539
1 row selected.
LOG#
Syntax#
LOG (m, n)
Description#
LOG returns the logarithm of n to base m. The base m can be any positive value other than 0 or 1, and n can be any positive value
Example#
<Query>
SELECT LOG(10, 100) FROM DUAL;
LOG(10, 100)
---------------
2
1 row selected.
MOD#
Syntax#
MOD (m, n)
Description#
MOD returns the remainder of division of m by n. If n is 0, a division by zero error is raised.
Examples#
<Query> Find the remainder of division of 10 by 3.
SELECT MOD(10, 3) FROM DUAL;
MOD(10, 3)
--------------
1
1 row selected.
<Query> Add up all salaries, divide the total by the lowest salary, and output the remainder.
SELECT MOD(SUM(salary), MIN(salary)) Remainder FROM employees;
REMAINDER
--------------
223000
1 row selected.
NUMAND#
Syntax#
NUMAND (bigint_a, bigint_b)
Description#
This function returns the result of the bitwise AND operation of bigint_a and bigint_b as the result of BIGINT type.
Example#
<Query>
SELECT NUMAND( 3, 5 ) FROM DUAL;
NUMAND( 3, 5 )
-----------------------
1
1 row selected.
NUMOR#
Syntax#
NUMOR (bigint_a, bigint_b)
Description#
This function returns the result of bitwise OR operation of bigint_a and bigint_b as the result of BIGINT type.
Example#
<Query>
SELECT NUMOR( 3, 5 ) FROM DUAL;
NUMOR( 3, 5 )
-----------------------
7
1 row selected.
NUMSHIFT#
Syntax#
NUMSHIFT (bigint, n)
Description#
This function returns the result of shifting bigint rightward n times to the result of BIGINT type.
Example#
<Query>
SELECT NUMSHIFT( 3, -5 ) FROM DUAL;
NUMSHIFT( 3, -5 )
-----------------------
96
1 row selected.
NUMXOR#
Syntax#
NUMXOR (bigint_a, bigint_b)
Description#
This function returns the result of bitwise XOR operation of bigint_a and bigint_b as the result of BIGINT type.
Example#
<Query>
SELECT NUMXOR( 3, 5 ) FROM DUAL;
NUMXOR( 3, 5 )
-----------------------
6
1 row selected.
POWER#
Syntax#
POWER (m, n)
Description#
POWER returns m, the base, raised to the power of n, the exponent. The base m and the exponent n can be any real numbers, but if the base is negative, then the exponent must be an integer.
Example#
<Query>
SELECT POWER(3, 2) FROM DUAL;
POWER(3, 2)
--------------
9
1 row selected.
RAND#
Syntax#
RAND ()
Description#
The RAND function returns a DOUBLE type value by generating arbitrary numbers ranging between less than 1 and greater than 0.
Example#
<Query>
SELECT RAND() FROM DUAL;
RAND
-------------------------
0.981041718735602
1 row selected.
RANDOM#
Syntax#
RANDOM (number)
Description#
RANDOM returns a pseudorandom integer value. The range of possible return values is from 0 to the maximum value of the INTEGER type, that is, 2,147,483,647.
If n is set to a nonzero number, that number will be used as the random seed value, on the basis of which a pseudorandom integer will be returned. If the RANDOM function is repeatedly called with the same nonzero seed value, the same value will be repeatedly returned. Set n to zero to prevent the same value from being repeatedly returned.
Examples#
<Query> Retrieve a random number without specifying a seed value.
SELECT RANDOM(0) FROM DUAL;
RANDOM(0)
--------------
16838
1 row selected.
<Query> Retrieve a random number using a seed value.
SELECT RANDOM(100) FROM DUAL;
RANDOM(100)
--------------
12662
1 row selected.
ROUND#
Syntax#
ROUND ( n1 [ , n2 ] )
Description#
ROUND returns n1 rounded to n2 places to the right of the decimal point. If n2 is omitted, then n1 is rounded to 0 (zero) decimal places. n2 can also be set to a negative number, in which case n1 will be rounded off n2 places to the left of the decimal point.
Example#
<Query> Return the results of the ROUND function on each of the two following expressions: ROUND(123.9994, 3), ROUND(123.9995, 3)
SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) FROM DUAL;
ROUND(123.9994, 3) ROUND(123.9995, 3)
-----------------------------------------
123.999 124
1 row selected.
<Query> Round the price of the most inexpensive product up to the nearest integer and output it.
SELECT ROUND( MIN(price) ) FROM goods;
ROUND( MIN(PRICE) )
----------------------
967
1 row selected.
| Example | Result |
|---|---|
| ROUND(748.58, -1) | 750 |
| ROUND(748.58, -2) | 700 |
| ROUND(748.58, -3) | 1000 |
ROUND always returns a value. If integer is a negative number that is greater than the number of digits to the left of the decimal point, ROUND will return 0.
| Example | Result |
|---|---|
| ROUND(748.58, -4) | 0 |
SIGN#
Syntax#
SIGN (number)
Description#
SIGN returns the sign of n, which can be any numeric data type. If n is positive, SIGN returns 1, whereas if n is negative, SIGN returns -1. if n is 0, SIGN returns 0.
Examples#
<Query> Return the results of the SIGN function on each of the following expressions
SELECT SIGN(15), SIGN(0), SIGN(-15) FROM DUAL;
SIGN(15) SIGN(0) SIGN(-15)
----------------------------------------
1 0 -1
1 row selected.
<Query> Output 1 if the wage is higher than 1000 dollars, -1 if it is lower than 1000 dollars, and 0 if it is exactly 1000 dollars.
SELECT e_firstname
, e_lastname
, SIGN(salary - 1000) As Wage_class
FROM employees;
E_FIRSTNAME E_LASTNAME WAGE_CLASS
------------------------------------------------------------
Chan-seung Moon
Susan Davenport 1
Ken Kobain 1
.
.
.
20 rows selected.
SIN#
Syntax#
SIN (n)
Description#
SIN is a trigonometric function that returns the trigonometric sine of n, expressed in radians. The return type is DOUBLE.
Example#
<Query> Retrieve the sine of a 30-degree angle.
SELECT SIN (30 * 3.14159265359/180) Sine_of_30_degrees FROM DUAL;
SINE_OF_30_DEGREES
---------------------
0.5
1 row selected.
SINH#
Syntax#
SINH (n)
Description#
SINX returns the hyperbolic sine of n. (SINH(n) = ( e n - e -n )/2)
Example#
<Query> Return the hyperbolic sine of 1:
SELECT SINH(1) Hyperbolic_sine_of_1 FROM DUAL;
HYPERBOLIC_SINE_OF_1
-----------------------
1.1752011936438
1 row selected.
SQRT#
Syntax#
SQRT (n)
Description#
SQRT returns the square root of n. n must not be a negative value.
Example#
<Query> Return the square root of 10.
SELECT SQRT(10) FROM DUAL;
SQRT(10)
--------------
3.162278
1 row selected.
TAN#
Syntax#
TAN (n)
Description#
TAN is a trigonometric function that returns the tangent of n, an angle expressed in radians. The return type is DOUBLE.
Example#
<Query> Retrieve the tangent of a 135-degree angle.
SELECT TAN (135 * 3.14159265359/180) Tangent_of_135_degrees FROM DUAL;
TANGENT_OF_135_DEGREES
-------------------------
-1
1 row selected.
TANH#
Syntax#
TANH (n)
Description#
TANH returns the hyperbolic tangent of n.
Example#
<Query> Return the hyperbolic tangent of 0.5.
SELECT TANH(.5) Hyperbolic_tangent_of_ FROM DUAL;
HYPERBOLIC_TANGENT_OF_
-------------------------
0.462117
1 row selected.
TRUNC(number)#
Syntax#
TRUNC ( n1 [ , n2 ] )
Description#
TRUNC returns n1 truncated to n2 decimal places. The return type is FLOAT.
If n2 is omitted, then n1 is truncated to an integer. n2 can be set to a negative number, in which case n1 will be truncated n2 places to the left of the decimal point.
Examples#
<Query> Return the results of the TRUNC function on each of the following expressions.
SELECT TRUNC(15.79, 1), TRUNC(15.79, -1) FROM DUAL;
TRUNC(15.79, 1) TRUNC(15.79, -1)
------------------------------------
15.7 10
1 row selected.
<Query> Retrieve the integer portion of the price of the most inexpensive product.
SELECT TRUNC(MIN(price)) FROM goods;
TRUNC(MIN(PRICE))
--------------------
966
1 row selected.
BITAND#
Syntax#
BITAND (bit_a, bit_b)
Description#
BITAND returns the result of a bitwise AND operation on bit_a and bit_b.
Example#
SELECT TO_CHAR( BITAND( BIT'01010101', BIT'10101010' ) ) FROM DUAL;
TO_CHAR( BITAND( BIT'01010101', BIT'1010
--------------------------------------------
00000000
1 row selected.
BITOR#
Syntax#
BITOR (bit_a, bit_b)
Description#
BITOR returns the result of a bitwise OR operation on bit_a and bit_b.
Example#
SELECT TO_CHAR( BITOR( BIT'01010101', BIT'10101010' ) ) FROM DUAL;
TO_CHAR( BITOR( BIT'01010101', BIT'10101
--------------------------------------------
11111111
BITXOR#
Syntax#
BITXOR (bit_a, bit_b)
Description#
BITXOR returns the result of a bitwise XOR (exclusive OR) operation on bit_a and bit_b.
Example#
SELECT TO_CHAR( BITXOR( BIT'01010101', BIT'10101010' ) ) FROM DUAL;
TO_CHAR( BITXOR( BIT'01010101', BIT'1010
--------------------------------------------
11111111
1 row selected.
BITNOT#
Syntax#
BITNOT (bit_a)
Description#
BITNOT returns the result of a bitwise NOT operation on bit_a.
Example#
SELECT TO_CHAR( BITNOT( BIT'01010101' ) ) FROM DUAL;
TO_CHAR( BITNOT( BIT'01010101' ) )
--------------------------------------
10101010
1 row selected.