OraclePLSQL--FUNCTIONS TYPES

FUNCTIONS

Types of function

Function Type	Applicable To
Arithmetic functions	Numerical data
Text functions	Alphanumeric data
Regular expression functions	Alphanumeric data
Date functions	Date/time-related data
General functions	Any datatype
Conversion functions	Datatype conversion
Group functions	Sets of values

Arithmetic Functions

Function	Description
ROUND(n[,m])	Round n on m decimal positions
TRUNC(n[,m])	Truncate n on m decimal positions
CEIL(n)	Round n upwards to an integer
FLOOR(n)	Round n downwards to an integer
ABS(n)	Absolute value of n
SIGN(n)	-1, 0, or 1 if n is negative, zero, or positive
SQRT(n)	Square root of n
EXP(n)	e (= 2,7182813...) raised to the nth power
LN(n),LOG(m,n)	Natural logarithm, and logarithm base m
POWER(n,m)	n raised to the mth power
MOD(n,m)	Remainder of n divided by m
SIN(n), COS(n), TAN(n)	Sine, cosine, and tangent of n (n expressed in radians)
ASIN(n), ACOS(n), ATAN(n)	Arcsine, arccosine, and arctangent of n
SINH(n), COSH(n), TANH(n)	Hyperbolic sine, hyperbolic cosine, and hyperbolic tangent of n

Common Oracle Text Functions

Function	Description
LENGTH(t)	Length (expressed in characters) of t
ASCII(t)	ASCII value of first character of t
CHR(n)	Character with ASCII value n
UPPER(t), LOWER(t)	t in uppercase/lowercase
INITCAP(t)	Each word in t with initial uppercase; remainder in lowercase
LTRIM(t[,k])	Remove characters from the left of t, until the first character not in k
RTRIM(t[,k])	Remove characters from the right of t, after the last character not in k
TRIM([[option][c FROM]]t)	Trim character c from t; option = LEADING, TRAILING, or BOTH
LPAD(t,n[,k])	Left-pad t with sequence of characters in k to length n
RPAD(t,n[,k])	Right-pad t with k to length n (the default k is a space)
SUBSTR(t,n[,m])	Substring of t from position n, m characters long (the default for m is until end)

Common Regular Expression Operators and Metasymbols

Operator	Type	Description
*	Postfix	Zero or more occurrences
+	Postfix	One or more occurrences
?	Postfix	Zero or one occurrence
|	Infix	Operator to separate alternative choices
^	Prefix	Beginning of a string, or position immediately following a newline character
$	Postfix	End of the line
.	"--"	Any single character
[[^]list]	"--"	One character out of a list; a circumflex (^) at the beginning works as a negation; a dash (-) between two characters works as a range indicator
()	"--"	Groups a (sub)expression, allowing you to refer to it further down in the expression
{m}	Postfix	Precisely m times
{m,}	Postfix	At least m times
{m,n}	Postfix	At least m times, and at most n times
\n	"--"	Refers back to the nth subexpression between parentheses (n is a digit between 1 and 9)

Syntax for Date/Time-Related Constants

Literal	Example
DATE 'yyyy-mm-dd'	DATE '2004-09-25'
TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff'[AT TIME ZONE '...']	TIMESTAMP '2004-09-25 23:59:59.99999' AT TIME ZONE 'CET'
TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff{+|-}hh:mi'	TIMESTAMP '2004-09-25 23:59:59.99 -5:00'
INTERVAL 'expr' <qualifier>	INTERVAL '1' YEAR
	INTERVAL '1 2:3' DAY TO MINUTE

Common Oracle Date Functions

Function	Description
ADD_MONTHS(d, n)	Date d plus n months
MONTHS_BETWEEN(d, e)	Months between dates d and e
LAST_DAY(d)	Last day of the month containing date d
NEXT_DAY(d, weekday)	The first weekday (mon, tue, etc.) after d
NEW_TIME(d, z1, z2)	Convert date/time from time zone z1 to z2
ROUND(d[, fmt])	d rounded on fmt (the default for fmt is midnight)
TRUNC(d[, fmt])	d truncated on fmt (the default for fmt is midnight)
EXTRACT(c FROM d)	Extract date/time component c from expression d

ROUND and TRUNC Date Formats

Format	Description
CC, SCC	Century, with or without minus sign (BC)
[S]YYYY, [S]YEAR, YYY, YY, Y	Year (in various appearances)
IYYY, IYY, IY, I	ISO year
Q	Quarter
MONTH, MON, MM, RM	Month (full name, abbreviated name, numeric, Roman numerals)
IW, WW	(ISO) week number
W	Day of the week
DDD, DD, J	Day (of the year/of the month/Julian day)
DAY, DY, D	Closest Sunday
HH, HH12, HH24	Hours
MI	Minutes

Common General Oracle Functions
Function	Description
GREATEST(a, b, …)	Greatest value of the function arguments
LEAST(a, b, …)	Least value of the function arguments
NULL IF(a, b)	NULL if a = b; otherwise a
COALESCE(a, b, …)	The first NOT NULL argument (and NULL if all arguments are NULL)
NVL(x, y)	y if x is NULL; otherwise x
NVL2(x, y, z)	y if x is not NULL; otherwise z
CASE x when a1 then b1
when a2 then b2 … else y
end
DECODE(x, a1, b1,	b1 if x = a1,
a2, b2,	b2 if x = a2, …
…, an, bn	bn if x = an,
[, y])	and otherwise y (or default: NULL)

Conversion Functions

Common Oracle Conversion Functions
Function	Description
TO_CHAR (n[,fmt])	Convert number n to a string
TO_CHAR(d[,fmt])	Convert date/time expression d to a string
TO_NUMBER (t)	Convert string t to a number
TO_BINARY_FLOAT (e[,fmt])	Convert expression e to a floating-point number
TO_BINARY_DOUBLE (e[,fmt])	Convert expression e to a double-precision, floating-point number
TO_DATE (t[,fmt])	Convert string t to a date
TO_YMINTERVAL(t)	Convert string t to a YEAR TO MONTH interval
TO_DSINTERVAL(t)	Convert string t to a DAY TO SECOND interval
TO_TIMESTAMP (t[,fmt])	Convert string t to a timestamp
CAST(e AS t)	Convert expression e to datatype t

Conversion Functions: Optional Format Components
Format	Description
[S]CC	Century; S stands for the minus sign (BC)
[S]YYYY	Year, with or without minus sign
YYY, YY, Y	Last 3, 2, or 1 digits of the year
[S]YEAR	Year spelled out, with or without minus sign (S)
BC, AD	BC/AD indicator
Q	Quarter (1,2,3,4)
MM	Month (01–12)
MONTH	Month name, padded with spaces to length 9
MON	Month name, abbreviated (three characters)
WW, IW	(ISO) week number (01–53)
W	Week number within the month (1–5)
DDD	Day number within the year (1–366)
DD	Day number within the month (1–31)
D	Day number within the week (1–7)
DAY	Day name, padded with spaces to length 9
DY	Day name abbreviation (three characters)
J	Julian date; day number since 01/01/4712 BC
AM, PM	AM/PM indicator
HH[12]	Hour within the day (01–12)
HH24	Hour within the day (00–23)
MI	Minutes within the hour (00–59)
SS	Seconds within the minute (00–59)
SSSSS	Seconds after midnight (0–86399)
/.,	Punctuation characters; displayed verbatim (between date fields)
"…"	String between double quotes displayed within the date expression

Common Oracle Group Functions
Function	Description	Applicable To
COUNT()	Number of values	All datatypes
SUM()	Sum of all values	Numeric data
MIN()	Minimum value	All datatypes
MAX()	Maximum value	All datatypes
AVG()	Average value	Numeric data
MEDIAN()	Median (middle value)	Numeric or date (time) data
STATS_MODE()	Modus (most frequent value)	All datatypes
STDDEV()	Standard deviation	Numeric data
VARIANCE()	Statistical variance	Numeric data

Valid and Invalid GROUP BY Syntax Examples

Syntax	Valid?
select a, b, max(c) from t ... group by a	No
select a, b, max(c) from t ... group by a,b	Yes
select a, count(b), min(c) from t ... group by a	Yes
select count(c) from t ... group by a	Yes