CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Numeric Functions
Version 11.1
 Have you seen our new Functions page? If not ... Click Here ... for instant access to all Oracle functions
General
Note: Demos referencing the Oracle sample schemas such as oe and sh have been copied from the documentation at http://tahiti.oracle.com for our convenience. Examples too complex for this page are referenced back to the original source where links may or may not be maintained.

Credit for their development belongs to Francisco Abedrabbo and his team.
 
ABS
Returns the absolute value of a number ABS(<value>)
SELECT ABS(-100) FROM DUAL;
 
ACOS
Returns the arc cosine of a number ACOS(<value>)
SELECT ACOS(0.5) ARC_COSINE FROM DUAL;
 
ASIN
Returns the arc sin of a number ASIN(<value>)
SELECT ASIN(0.5) ARC_SINE FROM DUAL;
 
ATAN
Returns the arc tanget of a number ATAN(<value>)
SELECT ATAN(0.5) ARC_TANGENT FROM DUAL;
 
ATAN2
Arc tangent of the first value divided by the arc tangent of the second ATAN2(<value>, <value>)
SELECT ATAN2(0.5, 0.4) ARC_TANGET_DIV FROM DUAL;
 
AVG
Returns the average of a column of numbers AVG(<value>)
SELECT AVG(initial_extent) FROM user_tables;

SELECT AVG( DISTINCT initial_extent) FROM user_tables;
 
BITAND
Computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers

Overload 1
BITAND(LEFT IN PLS_INTEGER, RIGHT IN PLS_INTEGER) RETURN PLS_INTEGER
TBD

Overload 2
BITAND(LEFT IN INTEGER, RIGHT IN INTEGER) RETURN INTEGER
conn oe/oe

SELECT order_id, customer_id,
 DECODE(BITAND(order_status,1),1, 'Warehouse','PostOffice')
 Location,
 DECODE(BITAND(order_status,2),2, 'Ground', 'Air') Method,
 DECODE(BITAND(order_status,4),4,'Insured','Certified') Receipt
FROM orders
WHERE order_status < 8;
 
CEIL
Smallest integer greater than or equal to a decimal value CEIL(<value>)
SELECT CEIL(12345.67) FROM DUAL;
 
COALESCE

Returns the first non-null value
COALESCE(<value>, <value>, ....)
CREATE TABLE test (
col1  NUMBER(3),
col2  NUMBER(3),
col3  NUMBER(3));

INSERT INTO test VALUES (1, NULL, NULL);
INSERT INTO test VALUES (NULL, 2, NULL);
INSERT INTO test VALUES (NULL, NULL, 3);
INSERT INTO test VALUES (1, NULL, 3);
INSERT INTO test VALUES (NULL, 2, 3);

SELECT * FROM test;

SELECT COALESCE(col1, col2, col3) FROM test;
 
CORR
Returns the coefficient of correlation of a set of number pairs For information see the CORR page or tahiti.oracle.com
 
CORR_K
The CORR_K function calculates the Pearson's correlation coefficient Pearson's correlation reflects the degree of linear relationship between two variables. It ranges from +1 to -1. A correlation of +1 means that there is a perfect positive linear relationship between variables.

For information see the CORR_K page or tahiti.oracle.com

 
CORR_S
The CORR_S function calculates the Spearman's rho correlation coefficient For information see CORR_S or tahiti.oracle.com
 
COS
Returns the cosine of a number (an angle expressed in radians) COS(<value>)
SELECT COS(180*3.1415926/180) COSINE FROM DUAL;
 
COSH
The hyperbolic cosine of a number COSH(<value>)
SELECT COSH(0) HYPERBOLIC_COS FROM DUAL;
 
COUNT
The number of rows returned by a query COUNT(<value>)
-- To create the all_objs table: Click Here

SELECT COUNT(*) FROM all_objs;
 
COVAR_POP

The population covariance of a set of number pairs
COVAR_POP(<expression1>, <expression2>)
conn sh/sh

SELECT t.calendar_month_number, 
COVAR_POP(s.amount_sold, s.amount_sold) AS CP,
COVAR_SAMP(s.amount_sold, s.amount_sold) AS CS
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.calendar_year = 1998
GROUP BY t.calendar_month_number;
 
COVAR_SAMP
The sample  covariance of a set of number pairs COVAR_SAMP(<expression1>, <expression2>)
See COVAR_POP demo.
 
CUME_DIST

Returns the cumulative distribution of a value in a group of values
CUME_DIST(<value>)
conn oe/oe

SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) CUME_DIST_OF_15500
FROM employees;
 
DENSE_RANK
Computes the rank of a row in an ordered group of rows DENSE_RANK(<value>)
conn oe/oe

SELECT DENSE_RANK(15500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) DENSE_RANK_OF_15500
FROM employees;
 
DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value
DUMP(<value> [,<return_format>[,<start_position>[,<length>]]]
 

8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
col drows format a30

SELECT table_name, num_rows, DUMP(num_rows) DROWS
FROM user_tables;

SELECT table_name, num_rows, DUMP(num_rows, 8) DROWS
FROM user_tables;
 
EXP

Returns e raised to to an exponential power
EXP(<value>)
SELECT 2.71828183 * 2.71828183 FROM DUAL;

SELECT EXP(2) FROM DUAL;

SELECT 2.71828183 * 2.71828183 * 2.71828183 FROM DUAL;

SELECT EXP(3) FROM DUAL;
 
FIRST

Returns the row ranked first using DENSE_RANK
SELECT <aggregate_function(column_name)> KEEP
(DENSE_RANK FIRST ORDER BY <column_name>)
FROM <table_name>
GROUP BY <column_name>;
conn oe/oe

SELECT department_id, 
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) WORST,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) BEST
FROM employees
GROUP BY department_id;
 
FLOOR
Returns the largest integer less than or equal to a decimal value FLOOR(<string_or_column>)
SELECT FLOOR(12345.67) FROM DUAL;
 
GREATEST
Returns the largest of multiple values GREATEST(<value>, <value>, .... )
SELECT GREATEST(9, 67.6, 10) FROM DUAL;
 
LAST
Returns the row ranked last using DENSE_RANK See LAST or the FIRST demo above.
 
LEAST
Returns the smallest of multiple values LEAST(<value>, <value>, ....)
SELECT LEAST(9, 67.6, 10) FROM DUAL;
 
LENGTH
Returns length in characters LENGTH(<value>)
SELECT bytes, LENGTH(bytes) FROM user_segments;
 
LENGTHB
Returns length in bytes LENGTHB(<value>)
SELECT bytes, LENGTHB(bytes) FROM user_segments;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.
 
LN
Returns the natural log of a number LN(<value>)
SELECT LN(2) NATURAL_LOG FROM DUAL;
 
LOG
Returns the logarithm, base m of n LOG(<m_value>,<n_value>)
SELECT LOG(10,100) FROM DUAL;

SELECT LOG(100,10) FROM DUAL;
 
MAX
Returns the maximum value returned by a query MAX(<column_name>)
SELECT MAX(initial_extent) FROM all_tables;
 
MEDIAN
Returns the middle value of a set MEDIAN(<column_name>)
SELECT MEDIAN(initial_extent) FROM all_tables;
 
MIN
Returns the minimum value returned by a query MIN(<column_name>)
SELECT MIN(initial_extent) FROM all_tables;
 
MOD
Returns the modulus of a number. Same as remainder except uses FLOOR MOD(<m_value>, <n_value>)
SELECT MOD(3, 2) FROM DUAL;

SELECT MOD(6, 2) FROM DUAL;
 
NANVL

Returns Alternate Number If The Value Is Not A Number
NANVL(<value_evaluated>, <value_returned>)
CREATE TABLE fpd (
dec_num    NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float  BINARY_FLOAT);

INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;

SELECT * FROM fpd;

SELECT bin_double, NANVL(bin_double, 0) FROM fpd;

SELECT bin_float, NANVL(bin_float, 0) FROM fpd;

INSERT INTO fpd VALUES ('NaN', 'NaN', 'NaN');
COMMIT;

SELECT bin_float, NANVL(dec_number, 0) FROM fpd;
 
NVL

Returns a Value if the Expression IS NULL
NVL(<expression>, <return_value>)
set serveroutput on

DECLARE
 i PLS_INTEGER;
BEGIN
  SELECT NVL(i, 93)
  INTO i
  FROM DUAL;

  dbms_output.put_line('i1: ' || i);

  SELECT NVL(i, 39)
  INTO i
  FROM DUAL;

  dbms_output.put_line('i2: ' || i);
END;
/
 
NVL2

Returns First Value if NULL, Second Value if NOT NULL
NVL2(<expression>, <return_if_value>, <return_if_not_null>)
CREATE TABLE t (
category  VARCHAR2(25),
outval    NUMBER(3),
inval     NUMBER(3));

INSERT INTO t VALUES ('Groceries', 10, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 100);
INSERT INTO t VALUES ('Groceries', 20, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 200);
INSERT INTO t VALUES ('Groceries', 30, NULL);

SELECT * FROM t;

SELECT category, SUM(NVL2(outval, -outval, inval)) NET
FROM t
GROUP BY category;
 
PERCENT_RANK
Calculates for a row r and a sort specification, the rank of row r minus 1 divided by the number of rows in the aggregate group PERCENT_RANK(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
conn oe/oe

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Percent-Rank" 
FROM employees;
 
PERCENTILE_CONT
Takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification PERCENTILE_CONT(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>);
conn oe/oe

SELECT department_id, PERCENTILE_CONT(0.5) 
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_CONT
FROM employees
GROUP BY department_id;
 
PERCENTILE_DISC

Takes a percentile value and a sort specification and returns an element from the set
PERCENTILE_DISC(<expression>) WITHIN GROUP
(ORDER BY <expression> <ASC | DESC> NULLS <FIRST | LAST>)
OVER (<query_partition_clause>);
conn oe/oe

SELECT department_id, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY salary DESC) MEDIAN_DISC
FROM employees
GROUP BY department_id;
 
POWER
Returns m_value raised to the n_value power POWER(<m_value>, <n_value>)
SELECT 2*2*2 FROM DUAL;

SELECT POWER(2,3) FROM DUAL;
 
RANK
Calculates the rank of a value in a group of values RANK(<column_name>) WITHIN GROUP
SELECT RANK(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) SAL_RANK
FROM employees;
 
REGR_ (Linear Regression) Functions
REGR_AVGX For more information go to http://tahiti.oracle.com

SELECT s.channel_id,
REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP,
REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD,
REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT,
REGR_INTERCEPT(s.quantity_sold, p.prod_list_price) INTCPT,
REGR_R2(s.quantity_sold, p.prod_list_price) RSQR,
REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE
FROM sales s, products p
WHERE s.prod_id=p.prod_id
AND p.prod_category='Women'
AND s.time_id=to_DATE('10-OCT-2000')
GROUP BY s.channel_id;
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
 
REMAINDER
Returns the modulus of a number (the remainder from dividing m by n. Same as mod except uses ROUND and doesn't use floor. REMAINDER(<m_value>, <n_value>)
SELECT REMAINDER(2,3) FROM DUAL;
 
ROUND
Returns a value rounded to integer places ROUND(<value>, <integer>)
SELECT ROUND(3.1415926, 4) FROM DUAL;
 
SIGN
Returns the sign of a number SIGN(<value>)
SELECT SIGN(15) FROM DUAL;
SELECT SIGN(0) FROM DUAL;
SELECT SIGN(-5) FROM DUAL;
 
SIN
Returns the sine of a number SIN(<value>)
SELECT SIN(2) SINE FROM DUAL;
 
SINH
Returns the hyperbolic sine of a number SINH(<value>)
SELECT SINH(2) HYPERBOLIC_SINE FROM DUAL;
 
SQRT
Returns the square root of a number SQRT(<value>)
SELECT SQRT(2) FROM DUAL;
 
STATS_ (Statistical) Functions
STATS_BINOMIAL_TEST For information go to tahiti.oracle.com
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST
STATS_WSR_TEST
STDDEV_POP
STDDEV_SAMP
 
STATS_MODE

Returns the value that occurs with the greatest frequency
STATS_MODE(<expression>)
conn oe/oe

SELECT department_id, STATS_MODE(salary) 
FROM employees
GROUP BY department_id;

SELECT salary, COUNT(*)
FROM employees
WHERE department_id = 50
GROUP BY salary;
 
STDDEV

Sample standard deviation of an expression
STDDEV(<expression>)
conn oe/oe

SELECT STDDEV(salary) AS DEVIATION FROM employees;
 
SUM
Computes the sum of an expression SUM(<column_name>)
SELECT SUM(initial_extent) FROM all_tables;

SELECT SUM( DISTINCT initial_extent) FROM all_tables;
 
TAN
Tangent in radians TAN(<value>)
SELECT TAN(135 * 3.14159265359/180) FROM DUAL;
 
TANH
Hyperbolic tangent TANH(<value>) RETURN NUMBER;
SELECT TANH(135 * 3.14159265359/180) FROM DUAL;
 
TRUNC
Truncates a Number or a Datetime to the Specified Number of Decimal Places.
Do not confuse this with the TRUNCATE function.
TRUNC(<value>, <decimal_places>) RETURN NUMBER;
SELECT TRUNC(15.79, 1) FROM DUAL;

SELECT TRUNC(15.79, -1) FROM DUAL;
 
VAR_POP
Population  Variance of a Set of Numbers VAR_POP(<column_name>) RETURN NUMBER;
SELECT VAR_POP(data_length) FROM all_tab_cols;
 
VAR_SAMP
Sample Variance of a Set of Numbers VAR_SAMP(<column_name>) RETURN NUMBER;
SELECT VAR_SAMP(data_length) FROM all_tab_cols;
 
VARIANCE
Variance of an Expression VARIANCE(<value>)
SELECT VARIANCE(initial_extent) FROM user_tables;
 
VSIZE
Byte Size VSIZE(e IN NUMBER) RETURN NUMBER
SELECT VSIZE(initial_extent) FROM all_tables;
 
WIDTH_BUCKET

Construct Equi-width Histograms

n+1 bucket is for overflow
WIDTH_BUCKET(<value>, <min_value>, <max_value>, <number_of_buckets>);
conn oe/oe

SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 4000, 10) CREDIT_GRP
FROM customers
WHERE nls_territory = 'SWITZERLAND'
ORDER BY credit_grp;
 
Related Topics
Analytic Functions
CAST
Conversion Functions
Date Functions
Data Mining Functions
Miscellaneous Functions
String Functions
XML Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [266 users online]    © 2010 psoug.org