Oracle Numeric Functions
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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;

 ABS(-100)
----------
       100
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;

ARC_COSINE
----------
1.04719755
Overload 2 ACOS(d IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
SELECT ACOS(0.5) ARC_COSINE FROM dual;
 
APPROX_COUNT
Returns the approximate count of an expression. With MAX_ERROR the function returns the maximum error between the actual and approximate count. APPROX_COUNT(<expression> [, 'MAX_ERROR']) RETURN NUMBER;
SELECT department_id, job_id, APPROX_COUNT(*)
FROM employees
GROUP BY department_id, job_id
HAVING APPROX_RANK (PARTITION BY department_id ORDER BY APPROX_COUNT(*) DESC) <= 10;
 
APPROX_COUNT_DISTINCT
Returns the approximate number of rows that contain the distinct value APPROX_COUNT_DISTINCT(<expression>) RETURN NUMBER;
conn / as sysdba

SQL> SELECT APPROX_COUNT_DISTINCT(object_name)
  2  FROM dba_objects
  3* WHERE object_name like '%A%';

APPROX_COUNT_DISTINCT(OBJECT_NAME)
----------------------------------
                             18159

SQL> SELECT COUNT(DISTINCT(object_name))
  2  FROM dba_objects
  3* WHERE object_name like '%A%';

COUNT(DISTINCT(OBJECT_NAME))
----------------------------
                       17902
 
APPROX_COUNT_DISTINCT_AGG
Takes as its input a column of details containing information about approximate distinct value counts, and performs count aggregations APPROX_COUNT_DISTINCT_AGG(<expression>);
CREATE MATERIALIZED VIEW monthly_prod_count_mv AS
SELECT year, month, APPROX_COUNT_DISTINCT_AGG(daily_detail) monthly_detail
FROM daily_prod_count_mv
GROUP BY year, month;
 
APPROX_COUNT_DISTINCT_DETAIL
Calculates information about the approximate number of rows that contain a distinct value returning a BLOB value, called a detail, containing that information in a special format APPROX_COUNT_DISTINCT_DETAIL(<expression>) RETURN BLOB;
CREATE MATERIALIZED VIEW daily_prod_count_mv AS
SELECT t.calendar_year AS YEAR, t.calendar_month_number AS MO_NUM,
t.day_number_in_month AS DAY,
APPROX_COUNT_DISTINCT_DETAIL(s.prod_id) AS DETAIL
FROM times t, sales s
WHERE t.time_id = s.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
 
APPROX_MEDIAN
Returns the approximate inverse distribution function that assumes a continuous distribution model. Returns an approximate middle value or an approximate interpolated value that would be the middle value once the values are sorted. Nulls are ignored. APPROX_MEDIAN(<expression> [DETERMINISTIC] [, <ERROR_RATE | CONFIDENCE>])
SELECT c.country_id country, APPROX_MEDIAN(s.amount_sold) AMOUNT_MEDIAN
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id
GROUP BY c.country_id;
 
APPROX_PERCENTILE
An approximate inverse distribution function taking a percentile value and a sort specification, and returns the value that would fall into that percentile value with respect to the sort specification. Nulls are ignored and provides an alternative to the PERCENTILE_CONT and PERCENTILE_DISC functions that return exact results while processing data significantly faster with negligible deviation from the exact result. APPROX_PERCENTILE(<expression> [DETERMINISTIC] [, <ERROR_RATE | CONFIDENCE>])
WITHIN GROUP (ORDER BY <expression> <ASC | DESC>);
SELECT department_id,
APPROX_PERCENTILE(0.25 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "25th Percentile Salary",
APPROX_PERCENTILE(0.50 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "50th Percentile Salary",
APPROX_PERCENTILE(0.75 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "75th Percentile Salary"
FROM employees
GROUP BY department_id
ORDER BY 1;
 
APPROX_PERCENTILE_AGG
Takes as its input a column of details containing approximate percentile information, and enables you to perform aggregations of that information APPROX_PERCENTILE_AGG(<expression>);
CREATE MATERIALIZED VIEW amt_sold_by_state_mv AS
SELECT country, state, APPROX_PERCENTILE_AGG(city_detail) AS DETAILS
FROM amt_sold_by_city_mv
GROUP BY country, state;
 
APPROX_PERCENTILE_DETAIL
Calculates approximate percentile information for the values of expr and returns a BLOB value, called a detail, which contains that information in a special format. This function is commonly used with the GROUP BY clause in a SELECT statement and calculates approximate percentile information within each group of rows and returns a single detail for each group. APPROX_PERCENTILE_DETAIL(<expression> [DETERMINISTIC]) RETURN BLOB;
CREATE MATERIALIZED VIEW amt_sold_by_city_mv
ENABLE QUERY REWRITE AS
SELECT c.country_id, c.cust_state_province, c.cust_city,
APPROX_PERCENTILE_DETAIL(s.amount_sold) AS DETAILS
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
GROUP BY country_id, cust_state_province, cust_city;
 
APPROX_RANK
Returns the approximate rank from an optional PARTITION BY clause followed by a mandatory ORDER BY ... DESC clause. The PARTITION BY key must be a subset of the GROUP BY key. The ORDER BY clause must include either APPROX_COUNT or APPROX_SUM. APPROX_MEDIAN(<expression> [PARTITION BY <partition_by_clause> [ORDER BY <order_by_clause> DESC])
SELECT department_id, job_id, APPROX_COUNT(*)
FROM employees
GROUP BY department_id, job_id
HAVING APPROX_RANK (PARTITION BY department_id ORDER BY APPROX_COUNT(*) DESC) <= 10;
 
APPROX_SUM
Returns the approximate sum of an expression. If you supply MAX_ERROR as the second argument, then the function returns the maximum error between the actual and approximate sum. You must use this function with a corresponding APPROX_RANK function in the HAVING clause. If a query uses APPROX_COUNT, APPROX_SUM, or APPROX_RANK, then the query must not use any other aggregation functions. APPROX_COUNT(<expression> [, 'MAX_ERROR']) RETURN NUMBER;
SELECT department_id, job_id,
APPROX_SUM(salary)
FROM employees
GROUP BY department_id, job_id
HAVING APPROX_RANK (PARTITION BY department_id
ORDER BY APPROX_SUM(salary) DESC) <= 10;
 
ASIN
Returns the arc sin of a number
Overload 1		 ASIN(n IN NUMBER) RETURN NUMBER;
SELECT ASIN(0.5) ARC_SINE FROM dual;

  ARC_SINE
----------
.523598776
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;

ARC_TANGENT
-----------
 .463647609
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;

ARC_TANGET_DIV
--------------
    .896055385
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;

 AVG(INITIAL_EXTENT)
-------------------
             262144

SELECT AVG(DISTINCT initial_extent) FROM user_tables;

 AVG(DISTINCTINITIAL_EXTENT)
---------------------------
                     262144
 
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;
SELECT BITAND(3,6) FROM dual;

BITAND(3,6)
-----------
          2

SELECT BITAND(6,3) FROM dual;

BITAND(60,30)
-------------
           28
Overload 2 BITAND(left IN INTEGER, right IN INTEGER) RETURN INTEGER;
conn oe/oe@pdbdev

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;

CEIL(12345.67)
--------------
         12346
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;

SQL> SELECT * FROM test;

      COL1       COL2       COL3
---------- ---------- ----------
         1
                    2
                               3
         1                     3
                    2          3

SELECT COALESCE(col1, col2, col3) FROM test;

COALESCE(COL1,COL2,COL3)
------------------------
                       1
                       2
                       3
                       1
                       2
 
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;

    COSINE
----------
        -1
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;

HYPERBOLIC_COS
--------------
             1
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;

  COUNT(*)
----------
     54922
 
COVAR_POP
The population covariance of a set of number pairs COVAR_POP(<expression1>, <expression2>) RETURN NUMBER;
conn sh/sh@pdbdev

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

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

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
WHERE rownum < 6;

TABLE_NAME        NUM_ROWS DROWS
--------------- ---------- -----------------------
TARGET_XTAB              8 Typ=2 Len=2: 193,9
EXT_TAB1                   NULL
BSR1_EXTTAB            992 Typ=2 Len=3: 194,10,93
LOADSHEET_XTAB         993 Typ=2 Len=3: 194,10,94
BS_CONSOL1               3 Typ=2 Len=2: 193,4

SELECT ut.table_name, ut.num_rows, DUMP(ut.num_rows, 8) DROWS
FROM user_tables ut
WHERE rownum < 6;

TABLE_NAME        NUM_ROWS DROWS
--------------- ---------- -----------------------
TARGET_XTAB              8 Typ=2 Len=2: 301,11
EXT_TAB1                         NULL
BSR1_EXTTAB            992 Typ=2 Len=3: 302,12,135
LOADSHEET_XTAB         993 Typ=2 Len=3: 302,12,136
BS_CONSOL1               3 Typ=2 Len=2: 301,4
 
EXP
Returns e raised to to an exponential power

Overload 1		 EXP(n IN NUMBER) RETURN NUMBER;
SELECT 2.71828183 * 2.71828183 FROM dual;

2.71828183*2.71828183
---------------------
           7.38905611

SELECT EXP(2) FROM dual;

    EXP(2)
----------
 7.3890561

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

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 WHERE rownum = 1;

     BYTES LENGTH(BYTES)
---------- -------------
    262144             6
 
LENGTHB
Returns length in bytes
Overload 1		 LENGTHB(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NUMBER;
SELECT bytes, LENGTHB(bytes) FROM user_segments WHERE rownum = 1;

     BYTES LENGTHB(BYTES)
---------- --------------
    262144              6
Overload 2 LENGTHB(bl BLOB) RETURN INTEGER;
conn pm/pm@pdbdev

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;

TABLE_NAME      LENGTHC(TABLE_NAME)
--------------- -------------------
TARGET_XTAB                      11
EXT_TAB1                          8
BSR1_EXTTAB                      11
LOADSHEET_XTAB                   14
BS_CONSOL1                       10
 
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;

TABLE_NAME      LENGTH4(TABLE_NAME)
--------------- -------------------
TARGET_XTAB                      11
EXT_TAB1                          8
BSR1_EXTTAB                      11
LOADSHEET_XTAB                   14
BS_CONSOL1                       10
 
LN
Returns the natural log of a number
Overload 1		 LN(n IN NUMBER) RETURN NUMBER;
SELECT LN(2) NATURAL_LOG FROM dual;

NATURAL_LOG
-----------
 .693147181
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;

LOG(10,100)
-----------
          2

SELECT LOG(100,10) FROM dual;

LOG(10,100)
-----------
         .5
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;

 MIN(INITIAL_EXTENT)
-------------------
              16384
 
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;

  MOD(3,2)
----------
         1

SELECT MOD(6, 2) FROM dual;

  MOD(6,2)
----------
         0
 
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@pdbdev

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

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

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;

REMAINDER(2,3)
--------------
            -1
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;
 
ROUND_TIES_TO_EVEN
Returns n rounded to integer places according to the following rules:
1. If integer is positive, n is rounded to integer places to the right of the decimal point.
2. If integer is not specified, then n is rounded to 0 places.
3. If integer is negative, then n is rounded to integer places to the left of the decimal point.		 ROUND_TIES_TO_EVEN(n [, INTEGER DESC])
SQL> SELECT round_ties_to_even(0.05, 1) "ROUND_EVEN" FROM dual;

ROUND_EVEN
----------
         0

SQL> SELECT round_ties_to_even(41.572,-1) "ROUND_EVEN" FROM dual;

ROUND_EVEN
----------
        40

SQL> SELECT round_ties_to_even(41.572,1) "ROUND_EVEN" FROM dual;

ROUND_EVEN
----------
      41.6

SQL> SELECT round_ties_to_even(41.572,2) "ROUND_EVEN" FROM dual;

ROUND_EVEN
----------
     41.57
 
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@pdbdev

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

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

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
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx