FUNCTIONS
Functions can be used in a
SELECT
statement item selection list,
WHERE
and
ORDER BY
clauses,
GROUP
BY
and
HAVING
clauses,
CONNECT BY
and
START WITH
clauses in hierarchical queries, plus
INSERT VALUES
and
UPDATE SET
clauses.
Single Row Functions
Execute an operation on each row of a query:
-
Strings:
-
INSTR(<string>,<substring>[,<position>[,occurrence>]])
-
LENGTH(<string>)
-
INITCAP(<string>)
-
LOWER(<string>)
-
UPPER(<string>)
-
LPAD(<string>,<n>[,<replace>)
-
RPAD LPAD(<string>,<n>[,<replace>)
-
LTRIM(<string>,<string>)
-
RTRIM(<string>,<string>)
-
trIM([[LEADINGTRAILINGBOTH] <character> FROM] <string>)
-
SUBSTR(<string>[,[-]<position> [,<n>]])
-
REPLACE(<string>,<search> [,<replace>])
-
Numbers:
-
ABS(<n>)
-
POWER(<n>,<exponent>)
-
SQRT(<n>)
-
CEIL(<n>)
-
FLOOR(<n>)
-
ROUND(<n>,<decimal places>)
-
TRUNC(<n>,<decimal places>)
-
MOD(<numerator>,<denominator>)
-
SIGN(<n>)
-
Datetime:
-
SYSDATE
-
CURRENT_DATE
-
CURRENT_TIMESTAMP(<precision>)
-
LOCALTIMESTAMP(<precision>)
-
NEXT_DAY(<date>,<weekday>)
-
LAST_DAY(<date>)
-
ADD_MONTHS(<date>,<months>)
-
MONTHS_BETWEEN(<date>,<date>)
-
EXTRACT(<format> FROM {<date><timestamp>)
-
ROUND(<date>[,<format>])
-
trUNC(<date>[,<format>])
-
Datatype conversions:
-
TO_NUMBER(<n>[,<format>])
-
TO_[N]CHAR(<string>[,<format>])
-
TO_DATE(<string>,<format>)
-
TO_[N]CHAR(<datetime>,<format>)
-
Miscellaneous:
-
DECODE(<expression>,<search>, <replace>
[,<search>,<replace>...],<default>)
-
NULLIF(<expression>,<expression>)
-
NVL(<expression>,<expression>)
-
NVL2(<expression>,<expression>,<expression>)
-
USER
-
USERENV(<parameter>)
-
GREATEST(<expression>[,<expression>...])
-
LEAST(<expression>[,<expression>...])
Aggregate Functions
Summarize repeating groups in a row set into distinct groups, creating values such as sums or averages:
-
Simple summaries:
-
COUNT(*[DISTINCTALL] <expression>)
-
AVG([DISTINCTALL] <expression>)
-
MIN([DISTINCTALL] <expression>)
-
MAX([DISTINCTALL] <expression>)
-
SUM([DISTINCTALL] <expression>)
-
Simple
statistics:
-
STDDEV([DISTINCTALL] <expression>)
-
VARIANCE([DISTINCTALL] <expression>)
-
CORR(<expression>,<expression>)
-
STDDEV_{POPSAMP}(<expression>)
-
VAR_{POPSAMP}(<expression>)
|