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([[LEADING|TRAILING|BOTH] <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(*|[DISTINCT|ALL] <expression>) AVG([DISTINCT|ALL] <expression>) MIN([DISTINCT|ALL] <expression>) MAX([DISTINCT|ALL] <expression>) SUM([DISTINCT|ALL] <expression>)
Simple statistics: STDDEV([DISTINCT|ALL] <expression>) VARIANCE([DISTINCT|ALL] <expression>) CORR(<expression>,<expression>) STDDEV_{POP|SAMP}(<expression>) VAR_{POP|SAMP}(<expression>)
|