*******************************************************************************
* SQL Group Functions (num can be a column or expression) *
(null values are ignored default between distinct and all is all)*
*******************************************************************************
AVG([distinct or all] num) average value
COUNT(distinct or all] num)number of values
MAX([distinct or all] num) maximum value
MAX([distinct or all] num) minimum value
STDDEV([distinct or all] num)standard deviation
SUM([distinct or all] num) sum of values
VARIANCE([distinct or all] num)variance of values
*******************************************************************************
* Miscellaneaous Functions :*
*******************************************************************************
DECODE(expr srch return [srch return] default]
if no search matches the expression then the default is returned
otherwise the first search that matches will cause
the corresponding return value to be returned
DUMP(column_name [fmt [start_pos [ length]]])
returns an internal oracle format used for getting info about a column
format options : = octal = decimel = hex = characters
return type codes : = varchar = number = long = date
= raw = long raw = rowid = char = mlslabel
GREATEST(expr [expr [ expr]]
returns the largest value of all expressions
LEAST(expr [expr [ expr]]
returns the smallest value of all expressions
NVL(expr expr
if expr is not null it is returned otherwise expr is returned
SQLCODE
returns sql error code of last errorCan not be used directly in query
value must be set to local variable first
SQLERRM
returns sql error message of last errorCan not be used directly in query
value must be set to local variable first
UID
returns the user id of the user you are logged on as
useful in selecting information from low level sys tables
USER
returns the user name of the user you are logged on as
USERENV(option)
returns information about the user you are logged on as
options : ENTRYID SESSIONID TERMINAL LANGUAGE LABEL OSDBA
(all options not available in all Oracle versions)
VSIZE(expr)
returns the number of bytes used by the expression
useful in selecting information about table space requirements
*******************************************************************************
* SQL Date Functions (dt represents oracle date and time) *
* (functions return an oracle date unless otherwise specified)*
*******************************************************************************
ADD_MONTHS(dt num) adds num months to dt (num can be negative)
LAST_DAY(dt)last day of month in month containing dt
MONTHS_BETWEEN(dt dt)returns fractional value of months between dt dt
NEW_TIME(dt tz tz)dt = date in time zone returns date in time zone
NEXT_DAY(dt str) date of first (str) after dt (str = Monday etc)
SYSDATE present system date
ROUND(dt [fmt] rounds dt as specified by format fmt
TRUNC(dt [fmt] truncates dt as specified by format fmt
*******************************************************************************
* Number Functions :*
*******************************************************************************
ABS(num)absolute value of num
CEIL(num) smallest integer > or = num
COS(num)cosine(num) num in radians
COSH(num) hyperbolic cosine(num)
EXP(num)e raised to the num power
FLOOR(num)largest integer < or = num
LN(num) natural logarithm of num
LOG(num num) logarithm base num of num
MOD(num num) remainder of num / num
POWER(num num) num raised to the num power
ROUND(num [num]num rounded to num decimel places (default )
SIGN(num) sign of num * if num =
SIN(num)sin(num) num in radians
SINH(num) hyperbolic sine(num)
SQRT(num) square root of num
TAN(num)tangent(num) num in radians
TANH(num) hyperbolic tangent(num)
TRUNC(num [num]truncate num to num decimel places (default )
*******************************************************************************
* String Functions String Result : *
*******************************************************************************
(num)ASCII character for num
CHR(num) ASCII character for num
CONCAT(str str) str concatenated with str (same as str||str)
INITCAP(str) capitalize first letter of each word in str
LOWER(str) str with all letters in lowercase
LPAD(str num [str])left pad str to length num with str (default spaces)
LTRIM(str [set])remove set from left side of str (default spaces)
NLS_INITCAP(str [nls_val])same as initcap for different languages
NLS_LOWER(str [nls_val])same as lower for different languages
REPLACE(str str [str])replaces str with str in str
deletes str from str if str is omitted
RPAD(str num [str])right pad str to length num with str (default spaces)
RTRIM(str [set])remove set from right side of str (default spaces)
SOUNDEX(str) phonetic representation of str
SUBSTR(str num [num])substring of str starting with num
num characters (to end of str if num is omitted)
SUBSTRB(str num [num]) same as substr but num num expressed in bytes
TRANSLATE(str set set) replaces set in str with set
if set is longer than set it will be truncated
UPPER(str) str with all letters in uppercase
*******************************************************************************
* String Functions Numeric Result :*
*******************************************************************************
ASCII(str) ASCII value of str
INSTR(str str [num [num]])position of numth occurrence of
str in str starting at num
(num num default to )
INSTRB(str str [num [num]])same as instr byte values for num num
LENGTH(str)number of characters in str
LENGTHB(str) number of bytes in str
NLSSORT(str [nls_val])nls_val byte value of str
**********************************
