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)