SQL Functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL. User functions are described in "User-Defined Functions". For information about functions used with Oracle interMedia, see Oracle8i interMedia Audio, Image, and Video User's Guide and Reference.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion".

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes, following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values. The general syntax is as follows:

function::=

single_row_function::=

The sections that follow list the built-in SQL functions in each of the groups illustrated above except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter.

The examples provided with the function descriptions use the EMP and DEPT tables that are part of the SCOTT schema in your sample Oracle database. Many examples also use a SALES table, which has the following contents:

REGION PRODUCT  S_DAY    S_MONTH     S_YEAR   S_AMOUNT   S_PROFIT
------ ------- ------ ---------- ---------- ----------   --------
200          1     10          6       1998      77586        586
200          1     26          8       1998      62109        509
200          1     11         11       1998      46632        432
200          1     14          4       1999      15678        278
201          1      9          6       1998      77972        587
201          1     25          8       1998      62418        510
201          1     10         11       1998      46864        433
201          1     13          4       1999      15756        279
200          2      9          6       1998      39087      293.5
200          2     25          8       1998      31310        255
200          2     10         11       1998      23533      216.5
200          2     13          4       1999       7979      139.5
201          2      9         11       1998    23649.5        217
201          2     12          4       1999     8018.5        140
200          3      9         11       1998      15834     144.67
200          3     12          4       1999    5413.33      93.33
201          3     11          4       1999       5440      93.67
200          4     11          4       1999       4131      70.25
201          4     10          4       1999    4151.25       70.5
200          5     10          4       1999       3362       56.4
201          5      5          6       1998      16068      118.2
201          5     21          8       1998    12895.6      102.8
201          5      9          4       1999     3378.4       56.6

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH clauses, and CONNECT BY clauses.

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The number functions are:

ABS ACOS ADD_MONTHS ATAN ATAN2 CEIL COS COSH

EXP FLOOR LN LOG MOD POWER ROUND (Number Function)

SIGN SIN SINH SQRT TAN TANH TRUNC (Number Function)

Character Functions Returning Character Values

Character functions that return character values, unless otherwise noted, return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message. The character functions that return character values are:

CHR CONCAT INITCAP LOWER LPAD LTRIM NLS_INITCAP

NLS_LOWER NLSSORT NLS_UPPER REPLACE RPAD RTRIM SOUNDEX

SUBSTR SUBSTRB TRANSLATE TRIM UPPER

Character Functions Returning Number Values

The character functions that return number values are:

ASCII INSTR

INSTRB LENGTH

LENGTHB

Date Functions

Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number. The date functions are:

ADD_MONTHS LAST_DAY MONTHS_BETWEEN

NEW_TIME NEXT_DAY ROUND (Date Function)

SYSDATE TRUNC (Date Function)

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:

CHARTOROWID CONVERT HEXTORAW NUMTODSINTERVAL NUMTOYMINTERVAL RAWTOHEX

ROWIDTOCHAR TO_CHAR (date conversion) TO_CHAR (number conversion) TO_DATE

TO_LOB TO_MULTI_BYTE TO_NUMBER TO_SINGLE_BYTE TRANSLATE ... USING

Miscellaneous Single-Row Functions

The following single-row functions do not fall into any of the other single-row function categories.

BFILENAME DUMP EMPTY_[B | C]LOB GREATEST LEAST NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID NLS_CHARSET_NAME NVL NVL2 SYS_CONTEXT

SYS_GUID UID USER USERENV VSIZE

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

See Also: In the description of SELECT, the "GROUP BY Examples" and the "HAVING" clause for more information on the GROUP BY clause and HAVING clauses.

Many (but not all) aggregate functions that take a single argument accept these options:

• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.

• ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither option, the default is ALL.

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

The aggregate functions are:

AVG CORR COUNT COVAR_POP COVAR_SAMP GROUPING

MAX MIN REGR_ (linear regression) functions STDDEV STDDEV_POP

STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

analytic_function::=

analytic_clause::=

query_partition_clause::=

ORDER_BY_clause::=

windowing_clause::=

The keywords and parameters of this syntax are:

analytic_function	is the name of an analytic function (see the listings of different types of analytic functions following this table).
arguments	Analytic functions take 0 to 3 arguments.
OVER analytic_clause	indicates that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
query_partition_clause
PARTITION BY	partitions the query result set into groups based on one or more value_expr. If you omit this clause, the function treats all rows of the query result set as a single group. You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
	Note: If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.
value_expr	Valid value expressions are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
ORDER_BY_clause
ORDER BY	specifies how data is ordered within a partition. You can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence. Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression. See Also: order_by_clause of "SELECT and Subqueries" for more information on this clause.
	Note: Analytic functions always operate on rows in the order specified in the ORDER_BY_clause of the function. However, the ORDER_BY_clause of the function does not guarantee the order of the result. Use the ORDER_BY_clause of the query to guarantee the final result ordering.
	Restriction: When used in an analytic function, the ORDER_BY_clause must take an expression (expr). Position (position) and column aliases (c_alias) are invalid. Otherwise this ORDER_BY_clause is the same as that used to order the overall query or subquery.
ASC | DESC	specifies the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST	specifies whether returned rows containing null values should appear first or last in the ordering sequence. NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
windowing_clause
ROWS | RANGE	These keywords define for each row a "window" (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top to bottom. ROWS specifies the window in physical units (rows). RANGE specifies the window as a logical offset. You cannot specify this clause unless you have specified the ORDER_BY_clause.
	Note: The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the ORDER_BY_clause to achieve this unique ordering.
BETWEEN ... AND	lets you specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.
	If you omit BETWEEN and specify only one end point, Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING	specifies that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
UNBOUNDED FOLLOWING	specifies that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
CURRENT ROW	As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING. As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.
value_expr PRECEDING value_expr FOLLOWING	For RANGE or ROW: If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING. If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.
	If you are defining a logical window defined by an interval of time in numeric format, you may need to use conversion functions. See Also: "NUMTOYMINTERVAL" and "NUMTODSINTERVAL" for information on converting numeric times into interval literals.
	If you specified ROWS: value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value. If value_expr is part of the start point, it must evaluate to a row before the end point. If you specified RANGE: value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. See Also: "Literals" for information on interval literals. You can specify only one expression in the ORDER_BY_clause If value_expr evaluates to a numeric value, the ORDER BY expr must be a NUMBER or DATE datatype. If value_expr evaluates to an interval value, the ORDER BY expr must be a DATE datatype.
If you omit the windowing_clause entirely, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Analytic functions are commonly used in data warehousing environments. The analytic functions are:

AVG CORR COVAR_POP COVAR_SAMP COUNT CUME_DIST DENSE_RANK LAG FIRST_VALUE LAST_VALUE

LEAD MAX MIN NTILE PERCENT_RANK RATIO_TO_REPORT RANK REGR_ (linear regression) functions ROW_NUMBER

STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE

See Also: Oracle8i Data Warehousing Guide for more information on these functions, and for scenarios illustrating their use.

Object Reference Functions

Object functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:

DEREF MAKE_REF

REF REFTOHEX

VALUE

See Also: Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals for more information about REFs.

Press "Next" to go to the first SQL function.