Function Quick Reference - Oracle


{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

  1. All comparison operators
  2. NOT
  3. AND
  4. 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


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 10n (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 YLast 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
QQuarter 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