A function is a command that manipulates data items and returns a single value.
The sections that follow show each SQL function and its related syntax. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.
See Also:
Functions in Oracle Database SQL Language Reference for detailed information about SQL functionsABS(n)
ACOS(n)
ADD_MONTHS(date, integer)
Aggregate functions return a single result row based on groups of rows, rather than on single rows.
analytic_function([ arguments ]) OVER (analytic_clause)
APPENDCHILDXML ( XMLType_instance, XPath_string, value_expr [, namespace_string ])
ASCII(char)
ASCIISTR(char)
ASIN(n)
ATAN(n)
ATAN2(n1 , n2)
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
BFILENAME('directory', 'filename')
BIN_TO_NUM(expr [, expr ]... )
BITAND(expr1, expr2)
CARDINALITY(nested_table)
CAST({ expr | MULTISET (subquery) } AS type_name)
CEIL(n)
CHARTOROWID(char)
CHR(n [ USING NCHAR_CS ])
CLUSTER_ID ( [ schema . ] model mining_attribute_clause )
CLUSTER_PROBABILITY ( [ schema . ] model [ , cluster_id ] mining_attribute_clause )
CLUSTER_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause )
COALESCE(expr [, expr ]...)
COLLECT( [ DISTINCT | UNIQUE ] column [ ORDER BY expr ] )
COMPOSE(char)
CONCAT(char1, char2)
CONVERT(char, dest_char_set[, source_char_set ])
CORR(expr1, expr2) [ OVER (analytic_clause) ]
{ CORR_K | CORR_S }
(expr1, expr2
[, { COEFFICIENT
| ONE_SIDED_SIG
| ONE_SIDED_SIG_POS
| ONE_SIDED_SIG_NEG
| TWO_SIDED_SIG
}
]
)
COS(n)
COSH(n)
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ]
COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
CUBE_TABLE
( ' { schema.cube [ {HIERARCHY | HRR} dimension hierarchy ]...
| schema.dimension [ {HIERARCHY | HRR} [dimension] hierarchy ]
}
'
)
CUME_DIST(expr[,expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
CURRENT_DATE
CURRENT_TIMESTAMP [ (precision) ]
CV([ dimension_column ])
DATAOBJ_TO_PARTITION( table, partition_id )
DBTIMEZONE
DECODE(expr, search, result [, search, result ]... [, default ])
DECOMPOSE( string [, { 'CANONICAL' | 'COMPATIBILITY' } ] )
DELETEXML( XMLType_instance, XPath_string [, namespace_string ])
DENSE_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[,expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
DEPTH(correlation_integer)
DEREF(expr)
DUMP(expr[, return_fmt [, start_position [, length ] ]])
{ EMPTY_BLOB | EMPTY_CLOB }( )
EXISTSNODE
(XMLType_instance, XPath_string
[, namespace_string ]
)
EXP(n)
EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)
EXTRACT(XMLType_instance, XPath_string [, namespace_string ])
EXTRACTVALUE(XMLType_instance, XPath_string [, namespace_string ])
FEATURE_ID( [ schema . ] model mining_attribute_clause )
FEATURE_SET( [ schema . ] model [, topN [, cutoff ]] mining_attribute_clause )
FEATURE_VALUE( [ schema . ] model [, feature_id ] mining_attribute_clause )
aggregate_function
KEEP
(DENSE_RANK FIRST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]
FIRST_VALUE
{ (expr) [ {RESPECT | IGNORE} NULLS ]
| (expr [ {RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)
FLOOR(n)
FROM_TZ (timestamp_value, time_zone_value)
GREATEST(expr [, expr ]...)
GROUP_ID( )
GROUPING(expr)
GROUPING_ID(expr [, expr ]...)
HEXTORAW(char)
INITCAP(char)
INSERTCHILDXML ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTCHILDXMLAFTER ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTCHILDXMLBEFORE ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTXMLAFTER ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
INSERTXMLBEFORE ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])
ITERATION_NUMBER
LAG
{ ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
aggregate_function KEEP
(DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]
LAST_DAY(date)
LAST_VALUE
{ (expr) [ { RESPECT | IGNORE } NULLS ]
| (expr [ { RESPECT | IGNORE } NULLS ])
OVER (analytic_clause)
LEAD
{ ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
LEAST(expr [, expr ]...)
{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}
(char)
LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
LN(n)
LNNVL(condition)
LOCALTIMESTAMP [ (timestamp_precision) ]
LOG(n2, n1)
LOWER(char)
LPAD(expr1, n [, expr2 ])
LTRIM(char [, set ])
MAKE_REF({ table | view } , key [, key ]...)
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MEDIAN(expr) [ OVER (query_partition_clause) ]
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MOD(n2, n1)
MONTHS_BETWEEN(date1, date2)
NANVL(n2, n1)
NCHR(number)
NEW_TIME(date, timezone1, timezone2)
NEXT_DAY(date, char)
NLS_CHARSET_DECL_LEN(byte_count, char_set_id)
NLS_CHARSET_ID(string)
NLS_CHARSET_NAME(number)
NLS_INITCAP(char [, 'nlsparam' ])
NLS_LOWER(char [, 'nlsparam' ])
NLS_UPPER(char [, 'nlsparam' ])
NLSSORT(char [, 'nlsparam' ])
NTH_VALUE(measure_expr, n)
[ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ]
OVER (analytic_clause)
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
NULLIF(expr1, expr2)
NUMTODSINTERVAL(n, 'interval_unit')
NUMTOYMINTERVAL(n, 'interval_unit')
NVL(expr1, expr2)
NVL2(expr1, expr2, expr3)
ORA_DST_AFFECTED(datetime_expr)
ORA_DST_CONVERT(datetime_expr [, integer [, integer ]])
ORA_DST_ERROR(datetime_expr)
ORA_HASH(expr [, max_bucket [, seed_value ] ])
PATH(correlation_integer)
PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
]...
)
PERCENT_RANK( ) OVER ([ query_partition_clause ] order_by_clause)
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
POWER(n2, n1)
POWERMULTISET(expr)
POWERMULTISET_BY_CARDINALITY(expr, cardinality)
PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )
PREDICTION_BOUNDS ( [schema.] model [, confidence_level [, class_value]] mining_attribute_clause )
PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause mining_attribute_clause )
PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )
PREDICTION_PROBABILITY ( [ schema . ] model [ , class ] mining_attribute_clause )
PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ] [ cost_matrix_clause ] mining_attribute_clause )
PRESENTNNV(cell_reference, expr1, expr2)
PRESENTV(cell_reference, expr1, expr2)
PREVIOUS(cell_reference)
RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
RANK( ) OVER ([ query_partition_clause ] order_by_clause)
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
RAWTOHEX(raw)
RAWTONHEX(raw)
REF (correlation_variable)
REFTOHEX (expr)
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
REGEXP_INSTR (source_char, pattern
[, position
[, occurrence
[, return_opt
[, match_param
[, subexpr]
]
]
]
]
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)
REGEXP_SUBSTR(source_char, pattern
[, position
[, occurrence
[, match_param
[, subexpr
]
]
]
]
)
REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY
{ REGR_SLOPE
| REGR_INTERCEPT
| REGR_COUNT
| REGR_R2
| REGR_AVGX
| REGR_AVGY
| REGR_SXX
| REGR_SYY
| REGR_SXY
}
(expr1 , expr2)
[ OVER (analytic_clause) ]
REMAINDER(n2, n1)
REPLACE(char, search_string
[, replacement_string ]
)
ROUND(date [, fmt ])
ROUND(n [, integer ])
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
ROWIDTOCHAR(rowid)
ROWIDTONCHAR(rowid)
RPAD(expr1 , n [, expr2 ])
RTRIM(char [, set ])
SCN_TO_TIMESTAMP(number)
SESSIONTIMEZONE
SET (nested_table)
SIGN(n)
SIN(n)
SINH(n)
SOUNDEX(char)
SQRT(n)
STATS_BINOMIAL_TEST(expr1, expr2, p
[, { TWO_SIDED_PROB
| EXACT_PROB
| ONE_SIDED_PROB_OR_MORE
| ONE_SIDED_PROB_OR_LESS
}
]
)
STATS_CROSSTAB(expr1, expr2
[, { CHISQ_OBS
| CHISQ_SIG
| CHISQ_DF
| PHI_COEFFICIENT
| CRAMERS_V
| CONT_COEFFICIENT
| COHENS_K
}
]
)
STATS_F_TEST(expr1, expr2
[, { { STATISTIC
| DF_NUM
| DF_DEN
| ONE_SIDED_SIG
} , expr3
| TWO_SIDED_SIG
}
]
)
STATS_KS_TEST(expr1, expr2
[, { STATISTIC | SIG } ]
)
STATS_MODE(expr)
STATS_MW_TEST(expr1, expr2
[, { STATISTIC
| U_STATISTIC
| ONE_SIDED_SIG , expr3
| TWO_SIDED_SIG
}
]
)
STATS_ONE_WAY_ANOVA(expr1, expr2
[, { SUM_SQUARES_BETWEEN
| SUM_SQUARES_WITHIN
| DF_BETWEEN
| DF_WITHIN
| MEAN_SQUARES_BETWEEN
| MEAN_SQUARES_WITHIN
| F_RATIO
| SIG
}
]
)
STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED
{
STATS_T_TEST_ONE ( expr1 [, expr2 ]
|
{ { STATS_T_TEST_PAIRED
| STATS_T_TEST_INDEP
| STATS_T_TEST_INDEPU
} ( expr1, expr2
}
}
[, { { STATISTIC | ONE_SIDED_SIG } , expr3 | TWO_SIDED_SIG | DF } ] )
STATS_WSR_TEST(expr1, expr2
[, { STATISTIC
| ONE_SIDED_SIG
| TWO_SIDED_SIG
}
]
)
STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
STDDEV_POP(expr) [ OVER (analytic_clause) ]
STDDEV_SAMP(expr) [ OVER (analytic_clause) ]
{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
SYS_CONNECT_BY_PATH(column, char)
SYS_CONTEXT('namespace', 'parameter' [, length ])
SYS_DBURIGEN({ column | attribute }
[ rowid ]
[, { column | attribute }
[ rowid ]
]...
[, 'text ( )' ]
)
SYS_EXTRACT_UTC(datetime_with_timezone)
SYS_GUID( )
SYS_TYPEID(object_type_value)
SYS_XMLAGG(expr [, fmt ])
SYS_XMLGEN(expr [, fmt ])
SYSDATE
SYSTIMESTAMP
TAN(n)
TANH(n)
TIMESTAMP_TO_SCN(timestamp)
TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])
TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])
TO_BLOB ( raw_value )
TO_CHAR(nchar | clob | nclob)
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
TO_CLOB(lob_column | char)
TO_DATE(char [, fmt [, 'nlsparam' ] ])
TO_DSINTERVAL ( ' { sql_format | ds_iso_format } ' )
TO_LOB(long_column)
TO_MULTI_BYTE(char)
TO_NCHAR({char | clob | nclob})
TO_NCHAR({ datetime | interval }
[, fmt [, 'nlsparam' ] ]
)
TO_NCHAR(n [, fmt [, 'nlsparam' ] ])
TO_NCLOB(lob_column | char)
TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])
TO_SINGLE_BYTE(char)
TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])
TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])
TO_YMINTERVAL
( ' { [+|-] years - months
| ym_iso_format
} ' )
TRANSLATE(expr, from_string, to_string)
TRANSLATE ( char USING
{ CHAR_CS | NCHAR_CS }
)
TREAT(expr AS [ REF ] [ schema. ]type)
TRIM([ { { LEADING | TRAILING | BOTH }
[ trim_character ]
| trim_character
}
FROM
]
trim_source
)
TRUNC(date [, fmt ])
TRUNC(n1 [, n2 ])
TZ_OFFSET({ 'time_zone_name'
| '{ + | - } hh : mi'
| SESSIONTIMEZONE
| DBTIMEZONE
}
)
UID
UNISTR( string )
UPDATEXML
(XMLType_instance,
XPath_string, value_expr
[, XPath_string, value_expr ]...
[, namespace_string ]
)
UPPER(char)
USER
[ schema. ]
{ [ package. ]function | user_defined_operator }
[ @ dblink. ]
[ ( [ [ DISTINCT | ALL ] expr [, expr ]... ] ) ]
USERENV('parameter')
VALUE(correlation_variable)
VAR_POP(expr) [ OVER (analytic_clause) ]
VAR_SAMP(expr) [ OVER (analytic_clause) ]
VARIANCE([ DISTINCT | ALL ] expr)
[ OVER (analytic_clause) ]
VSIZE(expr)
WIDTH_BUCKET (expr, min_value, max_value, num_buckets)
XMLAGG(XMLType_instance [ order_by_clause ])
XMLCAST ( value_expression AS datatype )
XMLCDATA ( value_expr )
XMLCOLATTVAL
(value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)
XMLCOMMENT ( value_expr )
XMLCONCAT(XMLType_instance [, XMLType_instance ]...)
XMLDIFF ( XMLType_document, XMLType_document [ , integer, string ] )
XMLELEMENT
( [ ENTITYESCAPING | NOENTITYESCAPING ]
[ NAME ]
{ identifier
| EVALNAME value_expr
}
[, XML_attributes_clause ]
[, value_expr [ [AS] c_alias ]]...
)
XMLEXISTS ( XQuery_string [ XML_passing_clause ] )
XMLFOREST
( value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)
XMLISVALID ( XMLType_instance [, XMLSchema_URL [, element ]] )
XMLPARSE
({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
)
XMLPATCH ( XMLType_document, XMLType_document )
XMLPI
( { [ NAME ] identifier
| EVALNAME value_expr
} [, value_expr ]
)
XMLQUERY ( XQuery_string [ XML_passing_clause ] RETURNING CONTENT [NULL ON EMPTY] )
XMLROOT
( value_expr, VERSION
{ value_expr | NO VALUE }
[, STANDALONE { YES | NO | NO VALUE } ]
)
XMLSEQUENCE( XMLType_instance
| sys_refcursor_instance [, fmt ]
)
XMLSERIALIZE
( { DOCUMENT | CONTENT } value_expr [ AS datatype ]
[ ENCODING xml_encoding_spec ]
[ VERSION string_literal ]
[ NO INDENT | { INDENT [SIZE = number] } ]
[ { HIDE | SHOW } DEFAULTS ]
)
XMLTABLE ( [ XMLnamespaces_clause , ] XQuery_string XMLTABLE_options )
XMLTRANSFORM(XMLType_instance, { XMLType_instance
| string
}
)