This chapter presents the syntax for SQL functions.
This chapter includes the following section:
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.
ABS(n)
ACOS(n)
ADD_MONTHS(date, integer)
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 (column)
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) ]
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 ])
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 { datetime_value_expression | interval_value_expression } )
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 [ 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 ] )
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 ]) 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 [ IGNORE NULLS ]) OVER (analytic_clause)
LEAD(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
LEAST(expr [, expr ]...)
{ LENGTH | LENGTHB | LENGTHC | LENGTH2 | LENGTH4 } (char)
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' ])
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_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_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_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] )
REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] )
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_INDEP | STATS_T_TEST_INDEPU | STATS_T_TEST_ONE | STATS_T_TEST_PAIRED } (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_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(char [, 'nlsparam' ])
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(char)
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 | DBTMEZONE } )
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 ])
XMLCOLATTVAL (value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLCOMMENT ( value_expr )
XMLCDATA ( value_expr )
XMLCONCAT(XMLType_instance [, XMLType_instance ]...)
XMLELEMENT ( [ NAME ] identifier [, XML_attributes_clause ] [, value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLFOREST ( value_expr [ AS c_alias ] [, value_expr [ AS c_alias ] ]... )
XMLPARSE ({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ] )
XMLPI ( [ NAME ] identifier [, value_expr ] )
XMLQUERY ( XQuery_string [ XML_passing_clause ] RETURNING CONTENT )
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 ] )
XMLTABLE ( [ XML_namespaces_clause , ] XQuery_string XMLTABLE_options )
[ XML_passing_clause ] [ COLUMNS XML_table_column [, XML_table_column ]... ]
XMLTRANSFORM(XMLType_instance, XMLType_instance)