Oracle Numeric Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This page is a collection of built-in Oracle Database functions used to evaluate and manipulate strings. There are functions not listed here such as CASE, CAST, ANALYTIC, DATA MINING, and OLAP that and you will find links to them at the bottom of the page.

Additional undocumented string related functions can be found on the Undocumented Oracle page also linked at page bottom.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
Dependencies
STANDARD    
 
ABS
Returns the absolute value of a number
Overload 1
ABS(n IN NUMBER) RETURN NUMBER;
SELECT ABS(-100) FROM dual;
Overload 2 ABS(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT ABS(-100) FROM dual;
Overload 3 ABS(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ABS(-100) FROM dual;
Overload 4 ABS(i IN PLS_INTEGER) RETURN BINARY_INTEGER;
SELECT ABS(-100) FROM dual;
 
ACOS
Returns the arc cosine of a number
Overload 1
ACOS(n IN NUMBER) RETURN NUMBER;
SELECT ACOS(0.5) ARC_COSINE FROM dual;
Overload 2 ACOS(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ACOS(0.5) ARC_COSINE FROM dual;
 
ASIN
Returns the arc sin of a number
Overload 1
ASIN(n IN NUMBER) RETURN NUMBER;
SELECT ASIN(0.5) ARC_SINE FROM dual;
Overload 2 ASIN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ASIN(0.5) ARC_SINE FROM dual;
 
ATAN
Returns the arc tanget of a number
Overload 1
ATAN(n IN NUMBER) RETURN NUMBER;
SELECT ATAN(0.5) ARC_TANGENT FROM dual;
Overload 2 ATAN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ASIN(0.5) ARC_TANGET FROM dual;
 
ATAN2
Arc tangent of the first value divided by the arc tangent of the second
Overload 1
ATAN2(x IN NUMBER, y IN NUMBER) RETURN NUMBER;
SELECT ATAN2(0.5, 0.4) ARC_TANGET_DIV FROM dual;
Overload 2 ATAN2(x IN BINARY_DOUBLE, y IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ATAN2(0.5, 0.4) ARC_TANGET_DIV FROM dual;
 
AVG
Returns the average of a column of numbers AVG(<value>) RETURN NUMBER;
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@pdborcl

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;
 
CASE
See link at page bottom
 
CEIL
Smallest integer greater than or equal to a decimal value
Overload 1
CEIL(n IN NUMBER) RETURN NUMBER;
SELECT CEIL(12345.67) FROM dual;
Overload 2 CEIL(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT CEIL(12345.67) FROM dual;
Overload 3 CEIL(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT CEIL(12345.67) FROM dual;
 
COALESCE
Returns the first non-null value

Note ... this is significantly slower than NVL and should not be used as a substitute for NVL functionality.
COALESCE(<value>, <value>, ....) RETURN NUMBER;
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 go to tahiti.oracle.com
 
CORR_K
Calculates the Pearson's correlation coefficient For information go to tahiti.oracle.com
 
CORR_S
Calculates the Spearman's rho correlation coefficient For information go to tahiti.oracle.com
 
COS
Returns the cosine of a number (an angle expressed in radians)
Overload 1
COS(n IN NUMBER) RETURN NUMBER;
SELECT COS(180*3.1415926/180) COSINE FROM dual;
Overload 2 COS(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT COS(180*3.1415926/180) COSINE FROM dual;
 
COSH
The hyperbolic cosine of a number
Overload 1
COSH(n IN NUMBER) RETURN NUMBER;
SELECT COSH(0) HYPERBOLIC_COS FROM dual;
Overload 2 COSH(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT COSH(0) HYPERBOLIC_COS FROM dual;
 
COUNT
The number of rows returned by a query COUNT(<value>) RETURN NUMBER;
SELECT COUNT(*) FROM all_objects;
 
COVAR_POP
The population covariance of a set of number pairs COVAR_POP(<expression1>, <expression2>) RETURN NUMBER;
conn sh/sh@pdborcl

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_POP(<expression1>, <expression2>) RETURN NUMBER;
See the COVAR_POP demo
 
CUME_DIST
Returns the cumulative distribution of a value in a group of values CUME_DIST(<value>) RETURN NUMBER;
conn oe/oe@pdborcl

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>) RETURN NUMBER;
conn oe/oe@pdborcl

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

Overload 2

Overload 1 is VARCHAR2 and Overload 3 is DATE
DUMP(
e   IN VARCHAR2 CHARACTER SET ANY_CS, -- expression
df  IN PLS_INTEGER := NULL,           -- return format
sp  IN PLS_INTEGER := NULL,           -- starting position
len IN PLS_INTEGER := NULL)           -- length
RETURN VARCHAR2;

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 ut.table_name, ut.num_rows, DUMP(ut.num_rows) DROWS
FROM user_tables ut;

SELECT ut.table_name, ut.num_rows, DUMP(ut.num_rows, 8) DROWS
FROM user_tables ut;
 
EXP
Returns e raised to to an exponential power

Overload 1
EXP(n IN NUMBER) RETURN NUMBER;
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;
Overload 2 EXP(d IN BINARY_DOUBLE) BINARY_DOUBLE;
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@pdborcl

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
Overload 1
FLOOR(n IN NUMBER) RETURN NUMBER;
SELECT FLOOR(12345.67) FROM dual;
Overload 2 FLOOR(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT FLOOR(12345.67) FROM dual;
Overload 3 FLOOR(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT FLOOR(12345.67) FROM dual;
 
GREATEST
Returns the largest of multiple values
Overload 1
GREATEST(pattern IN NUMBER) RETURN NUMBER;
SELECT GREATEST(9, 67.6, 10) FROM dual;
Overload 11 GREATEST(pattern IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT GREATEST(9, 67.6, 10) FROM dual;
Overload 12 GREATEST(pattern IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT GREATEST(9, 67.6, 10) FROM dual;
Overload 13 GREATEST(pattern IN PLS_INTEGER) RETURN PLS_INTEGER;
SELECT GREATEST(9, 67, 10) FROM dual;
 
LAST
Returns the row ranked last using DENSE_RANK SELECT <aggregate_function(column_name)> KEEP
(DENSE_RANK LAST ORDER BY <column_name>)
FROM <table_name>
GROUP BY <column_name>;
See FIRST Demo Above
 
LEAST
Returns the smallest of multiple values
Overload 1
LEAST(pattern IN NUMBER) RETURN NUMBER;
SELECT LEAST(9, 67.6, 10) FROM dual;
Overload 11 LEAST(pattern IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT LEAST(9, 67.6, 10) FROM dual;
Overload 12 LEAST(pattern IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT LEAST(9, 67.6, 10) FROM dual;
Overload 13 LEAST(pattern IN PLS_INTEGER) RETURN PLS_INTEGER;
SELECT LEAST(9, 67.6, 10) FROM dual;
 
LENGTH
Returns length in characters
Overload 1
LENGTH(ch IN VARCHAR2) RETURN PLS_INTEGER;
SELECT bytes, LENGTH(bytes) FROM user_segments;
 
LENGTHB
Returns length in bytes
Overload 1
LENGTHB(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NUMBER;
SELECT bytes, LENGTHB(bytes) FROM user_segments;
Overload 2 LENGTHB(bl BLOB) RETURN INTEGER;
conn pm/pm@pdborcl

SELECT LENGTHB(ad_composite), LENGTHB(ad_sourcetext)
FROM print_media;
Overload 3 LENGTHB(ch CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
See Overload 2 Demo Above
 
LENGTHC
Returns length in characters LENGTHC(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT table_name, LENGTHC(table_name)
FROM user_tables
WHERE rownum < 6;
 
LENGTH2
Length of a string, in UCS2 code points LENGTH2(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT table_name, LENGTH2(table_name)
FROM user_tables
WHERE rownum < 6;
 
LENGTH4
Length of a string, in UCS4 code points LENGTH4(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT table_name, LENGTH4(table_name)
FROM user_tables
WHERE rownum < 6;
 
LN
Returns the natural log of a number
Overload 1
LN(n IN NUMBER) RETURN NUMBER;
SELECT LN(2) NATURAL_LOG FROM dual;
Overload 2 LN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT LN(2) NATURAL_LOG FROM dual;
 
LOG
Returns the logarithm, base m of n

Overload 1
LOG(left IN NUMBER, right IN NUMBER) RETURN NUMBER;
SELECT LOG(10,100) FROM dual;

SELECT LOG(100,10) FROM dual;
Overload 2 LOG(left IN BINARY_DOUBLE, right IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
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(n1 IN NUMBER, n2 IN NUMBER) RETURN NUMBER;
SELECT MOD(3, 2) FROM dual;

SELECT MOD(6, 2) FROM dual;
 
NANVL
Returns alternate number if the value is Not A Number

Overload 1
NANVL(n1 IN NUMBER, n2 IN NUMBER) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

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;
Overload 2 NANVL(f1 IN BINARY_FLOAT, f2 IN BINARY_FLOAT) RETURN BINARY_FLOAT;
See NANVL Overload 1 Demo Above
Overload 3 NANVL(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
See NANVL Overload 1 Demo Above
 
NVL
Returns a Value if the Expression IS NULL

Overload 3
NVL(n1 IN NUMBER, n2 IN NUMBER) RETURN NUMBER;
set serveroutput on

DECLARE
 i NUMBER;
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;
/
Overload 19 NVL(f1 IN BINARY_FLOAT, f2 IN BINARY_FLOAT) RETURN BINARY_FLOAT;
TBD
Overload 20 NVL(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
TBD
Overload 21 NVL(i1 IN BINARY_INTEGER, i2 IN BINARY_INTEGER) RETURN BINARY_INTEGER;
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>) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

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@pdborcl

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@pdborcl

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@pdborcl

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

Overload 1
POWER(n IN NUMBER, e IN NUMBER) RETURN NUMBER;
SELECT 2*2*2 FROM dual;

SELECT POWER(2,3) FROM dual;
Overload 2 POWER(d IN BINARY_DOUBLE, e IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
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
Overload 1
REMAINDER(n1 IN NUMBER, n2 IN NUMBER) RETURN NUMBER;
SELECT REMAINDER(2,3) FROM dual;
Overload 2 REMAINDER(f1 IN BINARY_FLOAT, f2 IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT REMAINDER(2,3) FROM dual;
Overload 3 REMAINDER(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT REMAINDER(2,3) FROM dual;
 
ROUND
Returns a value rounded to integer places
Overload 1
ROUND(left IN NUMBER, right IN PLS_INTEGER := 0) RETURN NUMBER;
SELECT ROUND(3.1415926, 4) FROM dual;
Overload 4 ROUND(left IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT ROUND(3.1415926) FROM dual;
Overload 5 ROUND(left IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ROUND(3.1415926) FROM dual;
Overload 6 ROUND(i IN PLS_INTEGER, places IN PLS_INTEGER := 0) RETURN PLS_INTEGER;
SELECT ROUND(3.1415926, 4) FROM dual;
 
SIGN
Returns the sign of a number

Overload 1
SIGN(n IN NUMBER) RETURN NUMBER;
SELECT SIGN(15) FROM dual;
SELECT SIGN(0) FROM dual;
SELECT SIGN(-5) FROM dual;
Overload 2 SIGN(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT SIGN(15) FROM dual;
SELECT SIGN(0) FROM dual;
SELECT SIGN(-5) FROM dual;
Overload 3 SIGN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT SIGN(15) FROM dual;
SELECT SIGN(0) FROM dual;
SELECT SIGN(-5) FROM dual;
Overload 4 SIGN(i IN PLS_INTEGER) RETURN PLS_INTEGER;
SELECT SIGN(15) FROM dual;
SELECT SIGN(0) FROM dual;
SELECT SIGN(-5) FROM dual;
 
SIN
Returns the sine of a number
Overload 1
SIN(n IN NUMBER) RETURN NUMBER;
SELECT SIN(2) SINE FROM dual;
Overload 2 SIN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT SIN(2) SINE FROM dual;
 
SINH
Returns the hyperbolic sine of a number
Overload 1
SINH(n IN NUMBER) RETURN NUMBER;
SELECT SINH(2) HYPERBOLIC_SINE FROM dual;
Overload 2 SINH(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT SINH(2) HYPERBOLIC_SINE FROM dual;
 
SQRT
Returns the square root of a number
Overload 1
SQRT(n IN NUMBER) RETURN NUMBER;
SELECT SQRT(2) FROM dual;
Overload 2 SQRT(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT SQRT(2) FROM dual;
Overload 3 SQRT(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
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_T_TEST_INDEP
STATS_T_TEST_INDEPU
STATS_T_TEST_ONE
STATS_T_TEST_PAIRED
STATS_WSR_TEST
 
STATS_MODE
Returns the value that occurs with the greatest frequency STATS_MODE(<expression>)
conn oe/oe@pdborcl

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>) RETURN NUMBER;
conn oe/oe@pdborcl

SELECT STDDEV(salary) AS DEVIATION FROM employees;
 
STDDEV_POP
computes the population standard deviation and returns the square root of the population variance STDDEV_POP(<expression>)
conn sh/sh@pdbdev

SELECT STDDEV_POP(s.amount_sold) AS POP_STD_DEV
FROM sales s;
 
STDDEV_SAMP
Computes the cumulative sample standard deviation and returns the square root of the sample variance STDDEV_SAMP(<expression>)
conn sh/sh@pdbdev

SELECT STDDEV_SAMP(s.amount_sold) AS SAMP_STD_DEV
FROM sales s;
 
SUM
Computes the sum of an expression SUM(<column_name>) RETURN NUMBER;
SELECT SUM(initial_extent) FROM all_tables;

SELECT SUM(DISTINCT initial_extent) FROM all_tables;
 
TAN
Tangent in radians
Overload 1
TAN(n IN NUMBER) RETURN NUMBER;
SELECT TAN(135 * 3.14159265359/180) FROM dual;
Overload 2 TAN(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT TAN(135 * 3.14159265359/180) FROM dual;
 
TANH
Hyperbolic tangent
Overload 1
TANH(n IN NUMBER) RETURN NUMBER;
SELECT TANH(135 * 3.14159265359/180) FROM dual;
Overload 2 TANH(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT TANH(135 * 3.14159265359/180) FROM dual;
 
TRUNC
Truncates a Number to the Specified Number of Decimal Places

Overload 1
TRUNC(n IN NUMBER, places IN PLS_INTEGER := 0) RETURN NUMBER;
SELECT TRUNC(15.79, 1) FROM dual;

SELECT TRUNC(15.79, -1) FROM dual;
Overload 4 TRUNC(f IN BINARY_FLOAT) RETURN BINARY_FLOAT;
SELECT TRUNC(15.79) FROM dual;
Overload 5 TRUNC(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT TRUNC(15.79, 1) FROM dual;
Overload 6 TRUNC(i IN PLS_INTEGER, places IN PLS_INTEGER := 0) RETURN PLS_INTEGER;
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>) RETURN NUMBER;
SELECT VARIANCE(initial_extent) FROM user_tables;
 
VSIZE
Returns the number of bytes storing a value 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@pdborcl

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
Built-in Functions
CASE
CAST
Character Set Functions
Collection Functions
Conversion Functions
Data Mining Functions
Date Functions
Miscellaneous Functions
OLAP Functions
Operators (Built-in)
Regular Expressions
REPLACE
String Functions
SUBSTRING
Timestamp Functions
TRANSLATE
Undocumented Oracle
XML Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved