Function Quick Reference - Oracle

Table of Contents [Hide/Show]

   Operators
      Comparison Operators
      Order of Precence
   Numeric Functions
   Character Functions
   Date Functions
      Notes
      Functions
   Data-Type Conversion Functions
      Functions
      Number Format Elements
      Date Format Elements
      Time Format Elements
      Other Format Elements
      Suffixes Affecting Number Display
   Aggregate Functions

{outline||<1> - |

.<1> - }

Operators¶

Comparison Operators¶

Operator	Usage
`BETWEEN m AND n`	between two values, inclusive. Lower limit is always first, independent of `ORDER BY` clause
`IN (list)`	match any of a list of values, same as `= ANY`
`LIKE`	match a character pattern - `%` (percent sign) = any sequence of zero or more characters; `_` (underscore) = a single character; `ESCAPE` character defines the escape character, which prefixes a percent sign or an underscore for it to be interpreted literally (e.g., `WHERE dname LIKE '%\_%' ESCAPE '\'` will find a match for `dname = 'HEAD_QUARTERS'`)
`IS NULL`	is a null value

Order of Precence¶

All comparison operators
NOT
AND
OR

Numeric Functions¶

Syntax	Usage
`ROUND(number, n)`	Rounds `number` to `n` places after the decimal point. If `n` is omitted, zero is assumed. If `n` is negative, then number is rounded to the left of the decimal point.
`TRUNC(number, n)`	Truncates `number` to `n` places after the decimal point. If `n` is omitted, zero is assumed. If `n` is negative, then number is truncated to the left of the decimal point to zeroes.
`MOD(m, n)`	Returns the remainder of `m` divided by `n`.

Character Functions¶

Syntax	Usage
`LOWER(string)`	Converts `string` to lowercase
`UPPER(string)`	Converts `string` to uppercase
`INITCAP(string)`	Converts the initial character of each word in `string` to uppercase, and the rest to lowercase
`CONCAT(string1, string2)`	Concatenates `string1` with `string2`; equivalent to `string1 \|\| string2`.
`SUBSTR(string, start, len)`	Extracts `len` characters from `expr`, starting at `start`. If `start` is negative, the count starts at the end of string. If `len` is omitted, all characters to the end of `string` are returned.
`INSTR(string, substring)`	Returns the numeric position of a substring within string, with 1 being the first position. Returns 0 if not found.
`LENGTH(string)`	Return the number of characters in `string`
`LPAD(string1, num, pad)`	Pads the left side of `string1` with enough `pad` characters to a total width of `num`, right-justifying the string
`RPAD(string1,num ,pad)`	Pads the right side of `string1` with enough `pad` characters to a total width of `num`, left-justifying the string
`TRIM(LEADING \| TRAILING \| BOTH, trimchar FROM trimsource)`	Trims leading or trailing characters (or both) from `trimsource`. (Oracle 8 onward)

Date Functions¶

Notes¶

Date literals are expressed as DD-MON-YY in single quotes
Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.

Functions¶

Syntax	Usage
`SYSDATE`	Returns the current date and time - e.g., `select sysdate from dual`;
`date + number`	Adds a number of days to `date` and returns a date
`TRUNC(date)`	Returns just the date portion of `date`
`date - number`	Subtracts a number of days from a `date` and returns a date
`date1 - date2`	Returns the number of days from `date1` to `date2`
`date + number/24`	Adds `number` of hours to `date`
`MONTHS_BETWEEN(date1, date2)`	Number of months between `date1` and `date2`. If `date1` is later than `date2`, the result is positive; otherwise it's negative.
`ADD_MONTHS(date, n)`	Adds `n` number of calendar months to `date`. The value of `n` must be an integer, and can be negative. If a month doesn't have enough days, then the last day of the month is used; for example, one month after Jan 31 is Feb 28 (or Feb 29 in a leap year).

Data-Type Conversion Functions¶

Functions¶

Syntax	Usage
`TO_CHAR(number \| date, [fmt], [nlsparams])`	Converts a number or date to a VARCHAR2, using `fmt` as the format model. (See below for format models). If `fmt` or `nlsparams` are omitted, the session defaults are used. *Number Conversion to a VARCHAR2* - `nlsparams` specifies decimal character, group separator, local currency symbol, and international currency symbol. These characters are returns by number format elements. *Date Conversion to a VARCHAR2* - `nlsparams` specifies the language in which month, day names, and abbreviations are returned.
`TO_NUMBER(char, [fmt] , [nlsparams])`	Converts a string containing digits to a number in the format specified by `fmt`. For details on `nlsparams`, refer to *TO_CHAR function for Number Conversion*.
`TO_DATE(char, [fmt] , [nlsparams]`	Converts a string representing a date to a date value in the format specified by `fmt`. If `fmt` is omitted, the format is `DD-MON-YY`. For details on `nlsparams`, refer to *TO_CHAR function for Date Conversion*.

Number Format Elements¶

Element	Description	Example	Result
`9`	Numeric position; number of 9s determine display width	`999999`	`1234`
`0`	Display leading zeroes	`99999`	`1234`
`$`	Floating dollar sign	`$999,999`	`$1,234`
`L`	Floating local currency symbol	`L999999`	`FF1234`
`.` (period)	Decimal point in position specified	`999999.99`	`1234`
`,` (comma)	Comma in position specified	`999,999`	`1,234`
`MI`	Minus signs to right for negative values	`999999MI`	`1234-`
`PR`	Parenthesize negative numbers	`999999PR`	`-1234`
`EEEE`	Scientific notation	`99.999EEEE`	`1.23E+03`
`V`	Multiply by `10ⁿ` (`n` = number of 9s after `V`)	`9999V99`	`123400`
`B`	Display zero values as blank, not `0`	`B9999.99`	`1234`

Date Format Elements¶

Element	Description
`SCC` or `CC`	Century; `S` prefixes BC date with `-` (minus sign)
Years in dates `YYYY` or `SYYYY`	Year; `S` prefixes BC date with `-` (minus sign)
`YYY` or `YY` or `Y`	Last three, two, or one digits of year
`RR`	Two digit year, rounded to the nearest century
`Y,YYYY`	Year with comma in this position
`IYYY`, `IYY`, `IY`, `I`	Four, three, two, or one digit year based on the ISO standard
`SYEAR` or `YEAR`	Year spelled out; S prefixes BC date with `-` (minus sign)
`BC` or `AD`	BC/AD indicator
`B.C.` or `A.D.`	BC/AD indicator with periods
`Q`	Quarter of year
`MM`	Month, two digit value
`MONTH`	Name of month padded with blanks to length of nine characters
`MON`	Name of month, three character abbreviation
`RM`	Roman Numeral month
`WW` or `W`	Week of year or month
`DDD` or `DD` or `D`	Day of year, month, or week
`DAY`	Name of day padded with blanks to a length of nine characters
`DY`	Name of day; three letter abbreviation
`J`	Julian day; the number of days since 31 December 4713 BC

Time Format Elements¶

Element	Description
`AM` or `PM`	Meridian indicator
`A.M.` or `P.M.`	Meridian indicator with periods
`HH` or `HH12` or `HH24`	Hour-of-day or hour (1-12) or hour (0-23)
`MI`	Minute (0-59)
`SS`	Second (0-59)
`SSSSS`	Seconds past midnight (0-86,399)

Other Format Elements¶

Element	Description
`/ . ,`	Punctuation is reproduced in the result
`"of the"`	Quoted string is reproduced in the result

Suffixes Affecting Number Display¶

Element	Description
`TH`	Ordinal number (for example `DDTH` for `4TH`)
`SP`	Spelled out number (for example `DDSP` for `FOUR`)
`SPTH` or `THSP`	Spelled out ordinal numbers (for example, `DDSPTH` for `FOURTH`)

Aggregate Functions¶

Syntax	Usage
`AVG([DISTINCT \| ALL] expr)`	Average value of `expr`, ignoring null values
`COUNT(* \| [DISTINCT \| ALL] expr)`	Number of rows, where `expr` evaluates to something non-null. Count all selected rows using `*` (asterisk), including duplicates and rows with nulls.
`MAX([DISTINCT \| ALL] expr)`	Maximum value of `expr`, ignoring null values
`MIN([DISTINCT \| ALL] expr)`	Minimum value of `expr`, ignoring null values
`STDDEV([DISTINCT \| ALL] expr)`	Standard deviation of `expr`, ignoring null values
`SUM([DISTINCT \| ALL] expr)`	Sum values of `expr`, ignoring null values
`VARIANCE([DISTINCT \| ALL] expr)`	Variance value of `expr`, ignoring null values