*******************************************************************************
* SQL Group Functions (num can be a column or expression) *
(null values are ignored
*******************************************************************************
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
if no search matches the expression then the default is returned
otherwise
the corresponding return value to be returned
DUMP(column_name [
returns an internal oracle format
format options :
return type codes :
GREATEST(expr [
returns the largest value of all expressions
LEAST(expr [
returns the smallest value of all expressions
NVL(expr
if expr
SQLCODE
returns sql error code of last error
value must be set to local variable first
SQLERRM
returns sql error message of last error
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(
returns information about the user you are logged on as
options : ENTRYID
(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
LAST_DAY(dt) last day of month in month containing dt
MONTHS_BETWEEN(dt
NEW_TIME(dt
NEXT_DAY(dt
SYSDATE present system date
ROUND(dt [
TRUNC(dt [
*******************************************************************************
* Number Functions : *
*******************************************************************************
ABS(num) absolute value of num
CEIL(num) smallest integer > or = num
COS(num) cosine(num)
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
MOD(num
POWER(num
ROUND(num
SIGN(num) sign of num *
SIN(num) sin(num)
SINH(num) hyperbolic sine(num)
SQRT(num) square root of num
TAN(num) tangent(num)
TANH(num) hyperbolic tangent(num)
TRUNC(num
*******************************************************************************
* String Functions
*******************************************************************************
(num) ASCII character for num
CHR(num) ASCII character for num
CONCAT(str
INITCAP(str) capitalize first letter of each word in str
LOWER(str) str with all letters in lowercase
LPAD(str
LTRIM(str [
NLS_INITCAP(str [
NLS_LOWER(str [
REPLACE(str
deletes str
RPAD(str
RTRIM(str [
SOUNDEX(str) phonetic representation of str
SUBSTR(str
num
SUBSTRB(str
TRANSLATE(str
if set
UPPER(str) str with all letters in uppercase
*******************************************************************************
* String Functions
*******************************************************************************
ASCII(str) ASCII value of str
INSTR(str
str
(num
INSTRB(str
LENGTH(str) number of characters in str
LENGTHB(str) number of bytes in str
NLSSORT(str [
**********************************
From:http://tw.wingwit.com/Article/program/Oracle/201311/17624.html