|
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. |
