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([[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>)