ABS(n)
Absolute
value of number
ACOS(n)
arc
cosine of n
ADD_MONTHS(date,num_months)
Returns
date + num_months
ASCII(char)
Converts
char into a decimal ascii code
ASIN(n)
arc
sine of n.
ATAN(n)
arc
tangent of n.
ATAN2(n.m)
arc
tangent of n and m.
AVG([DISTINCT]n)
Averge
value of 'n' ignoring NULLs
BETWEEN
value AND value
Where
'x' between 25 AND 100
BFILENAME('directory','filename')
Get
the BFILE locator associated with a physical LOB binary file
CEIL(n)
Round
n up to next whole number
CHARTOROWID(char)
Converts
a Char into a rowid value.
CHR(n)
Character
with value n
CONCAT(s1,s2)
Concatenate
string1 and string2
CONVERT
(char_to_convert, new_char_set, old_char_set)
Convert
character sets
COS(n)
Cosine
of number
COSH(n)
Hyperbolic
Cosine of number
COUNT(*)
Count
the no of rows returned
COUNT([DISTINCT]
expr)
Count
the no of rows returned by expr
DECODE
IF
x THEN return y ELSE return z
DENSE_RANK
Calculate
the rank of a value in a group
DEREF(e)
Return
the object reference of argument e.
DUMP(expr,fmt[,start,length])
Convert
to dec/hex/oct and display char set
EMPTY_BLOB
Return
an empty LOB locator (use to empty a column or variable)
EMPTY_CLOB
Return
an empty LOB locator (use to empty a column or variable)
EXISTS
Return
TRUE if a subquery returns at least one row
EXP(n)
Exponential
(e to 'n'th power)
FLOOR(n)
Round
n down to the next whole number.
GREATEST(expression,
expression...)
Returns
the largest in a list of expressions.
GROUPING
Grouping
for superaggregate rows=NULL (see GROUP BY ROLLUP/CUBE)
HEXTORAW(char)
Convert
char containing hex digits to a raw value.
IN
(list of comma separated values)
Effectively
a shorthand for ['x' = y OR 'x' = z...] i.e. Where 'x' IN ('sales','marketing','recruitment')
INITCAP(char)
String
with Initial Capitals
INSTR(str,
chars[,s[,n]])
Find
the 'n'th occurence of 'chars' in 'str' Starting at position 's' n and s
default to 1
INSTRB
(str, chars[,s[,n]])
The
same as INSTR, except that 's' and the return value are expressed in
bytes, use for double-byte char sets
IS
[NOT] NULL
Check
for NULL (empty) values Where 'x' IS NULL;
LAST_DAY(date)
Returns
the last day of month in Date
LEAST(expression,
expression...)
Returns
the smallest in a list of expressions
LENGTH(char)
Returns
the number of characters in char
LENGTHB(char)
Returns
the number of bytes in char (use for double-byte char sets)
LIKE
wildcard/value
Wildcards
are [% = any chars] [ _ = any one char]Where 'x' LIKE 'smith%' [will find
'Smithson'] Where 'x' LIKE 'smith_' [will find 'Smithy']
LN(n)
Natural
Log of n, where n>0
LOG(b,n)
log
of n, base b
LOWER(char)
Returns
Chars in lowercase
LPAD(char,
n[,PadChar])
Left
Pad char with n spaces [or PadChars] LTRIM(char[,set])Left Trim char -
remove leading spaces [or char set]
MAKE_REF(table,key)
Create
a REF to a row of an OBJECT view/table
MAX([DISTINCT]
expr)
Maximum
value returned by expr
MIN([DISTINCT]
expr)
Minimum
value returned by expr
MOD(x,y)
Remainder
of x divided by y
MONTHS_BETWEEN(end_date,
start_date)
Number
of months between the 2 dates (integer)
NEW_TIME
(date, zone1, zone2)
Convert
between GMT and US time zones (but not CET)
NEXT_DAY(date,day_of_week)
'12-OCT-01','Monday'
will return the next Mon after 12 Oct
NLS_CHARSET_DECL_LEN
(bytecount,charset)
Returns
the declaration width (no of chars) of an NCHAR column
NLS_CHARSET_ID
(varchars)
Returns
the char set ID given a charset name
NLS_CHARSET_NAME(charset_id)
Returns
the char set name given a charset id
NLS_INITCAP(char[,'NLS_SORT
= sort_sequence'])
Returns
char in Initial Caps, using an NLS sort_sequence either the session
default or specified directly
NLS_LOWER(char[,'NLS_SORT
= sort_sequence'])
Returns
char in lower case, using an NLS sort_sequence either the session default
or specified directly
NLSSORT(char[,'NLS_SORT
= sort_sequence'])
Return
the string of bytes used to sort char, using an NLS sort_sequence either
the session default or specified directly
NLS_UPPER
(char[,'NLS_SORT = sort_sequence'])
Returns
char in UPPER
case,
using an NLS sort_sequence either the session default or specified
directly
NVL(expression,
value_if_null)
If
expression is null, returns value_if_null; if expression
is not null, returns expression. The arguments can have any
datatype (Oracle will perform implicit conversion where needed).
PERCENT_RANK
Calculate
the percent rank of a value in a group.
POWER(m,n)
m
raised to the nth power
RANK
Calculate
the rank of a value in a group
RAWTOHEX(raw)
Convert
raw to a character value containing its hex equivalent
REF(table_alias)
Returns
a REF value for an object instance (bound to the variable or row.) The
table alias (correlation variable) is associated with one row of an object
table or an object view in an SQL statement.
REFTOHEX(ref)
Convert
ref (object type) to a char value containing its hex equivalent.
REPLACE(char,
search_str[, replace_str])
ANSI
alternative to decode()
Replace every occurrence of search_str with replace_str, replace_str
defaults to null.
ROUND(n,d)
n
rounded to d decimal places (d defaults to 0)
ROUND(date,fmt)
date
rounded to fmt
ROWIDTOCHAR(rowid)
Convert
a rowid value to VARCHAR2
ROW_NUMBER
Assign
a unique number to each row of results.
RPAD(char,
n[,PadChar])
Right
Pad char with n spaces [or PadChars]
RTRIM(char[,set])
Right
Trim char - remove trailing spaces [or char set]
SIGN(n)
positive
= 1, zero = 0, negative = -1
SIN(n)
Sine
of n in Radians
SINH(n)
Hyperbolic
Sine of n in Radians
SOUNDEX(char)
Returns
a char value representing the sound of the words
SQRT(n)
Square
Root (returns NULL for negative no's)
STDDEV([DISTINCT]
n)
Standard
deviation of n
SUBSTR(char,
s[,l])
A
substring of char, starting at character s, length l
SUBSTRB(char,
s[,l])
A
substring of char, starting at character s, length l. The same as SUBSTR,
except that 's', 'l' and the return value are expressed in bytes, use for
double-byte char sets
SUM([DISTINCT]
n)
Sum
of values of n, ignoring NULLs
SYS_CONTEXT('namespace','attribute_name')
Examine
the package associated with the context namespace.
Possible
attributes are:
NLS_TERRITORY,
NLS_CURRENCY,
NLS_CALENDAR,
NLS_DATE_FORMAT,
NLS_DATE_LANGUAGE,
NLS_SORT,
SESSION_USER
,
CURRENT_USER
,
CURRENT
SCHEMAID,
SESSION_USERID,
CURRENT_USERID,
CURRENT_SCHEMA
note:
CURRENT_USER
may
be different from SESSION_USER within a stored procedure (e.g an
invoker-rights procedure).
SYS_GUID()
Returns
a globally unique identifier (16 byte RAW value)
SYSDATE
The
current system date & time
TAN(n)
Tangent
of n in Radians
TANH(n)
Hyperbolic
tangent of n in Radians
TO_CHAR
Convert
to character String
TO_DATE
Convert
to date value
TO_LOB(long)
Convert
LONG values to CLOB or NCLOB values or convert LONG RAW values to BLOB
values. Use only as part of an "INSERT INTO ... SELECT..."
subquery.
TO_MULTI_BYTE(char)
Convert
single-byte char to multi-byte char.
TO_NUMBER
Convert
to numeric format
TO_SINGLE_BYTE(char)
Convert
multi-byte char to single-byte char.
TRANSLATE('char','search_str','replace_str')
Replace
every occurrence of search_str with replace_str unlike REPLACE() if
replace_str is NULL the function returns NULL
TRANSLATE
(text USING charset)
Convert
text into a specific character set. Use this instead of CONVERT
() if either the input or output datatype is
NCHAR or NVARCHAR2.
TRIM
(LEADING|TRAILING|BOTH trim_char FROM
trim_source)
Returns
trim_source as a VARCHAR2 with leading/trailing items removed trim_char
defaults to a space ' ' but may be numeric or char 'A' TRUNC
(i,d) i truncated to d decimal places (d
defaults to 0)
TRUNC
(date,fmt)
date
truncated to nearest fmt
UID
User
id - unique number
UPPER
(char)
Returns
Chars in uppercase
USER
Returns
the current Username
USERENV
('option')
Can
return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA,
LANG, INSTANCE, CLIENT_INFO
VALUE
(correlation_variable)
Return
the object instance for a row of an object table as associated with the
correlation_variable (table alias)
VARIANCE
([DISTINCT] n)
Variance
of n, ignoring NULLs
VSIZE
(expr)
Value
Size - returns the number of bytes used by each row of expr.
|