Oracle Numeric Functions
Version 11.2.0.3
 
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
 
General Information
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
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

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

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

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

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 NUMBER,               -- 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 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

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

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 See FIRST demo
 
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

SELECT LENGTHB(ad_composite), LENGTHB(ad_sourcetext)
FROM print_media;
Overload 3 LENGTHB(ch CLOB CHARACTER SET ANY_CS) RETURN INTEGER;
See overload 2 above
 
LENGTHC
Returns length in bytes LENGTHC(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT table_name, LENGTHC(table_name)
FROM user_tables
WHERE rownum < 6;
 
LENGTH2
Returns length in bytes LENGTHB(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NATURAL;
SELECT table_name, LENGTH2(table_name)
FROM user_tables
WHERE rownum < 6;
 
LENGTH4
Returns length in bytes LENGTHB(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;
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 above demo
Overload 3 NANVL(d1 IN BINARY_DOUBLE, d2 IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
See above demo
 
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;
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

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

SELECT STDDEV(salary) AS DEVIATION FROM employees;
 
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>)
SELECT VARIANCE(initial_extent) FROM user_tables;
 
VSIZE
Returns The Number Of Bytes Required By 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

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
CAST
Conversion Functions
Date Functions
Data Mining Functions
Miscellaneous Functions
String Functions
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-2013 Daniel A. Morgan All Rights Reserved