Common oracle Functions

Common oracle function summary ********************

**************************************** ***************************************
* 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, srch1, return1 [, srch2, return2. ..], 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: 8 = octal, 10 = decimel, 16 = hex, 17 = characters
-- Return type codes: 1 = varchar2, 2 = number, 8 = long, 12 = date,
-- 23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel
GREATEST (expr [, expr2 [, expr3. ..]
-- Returns the largest value of all expressions
LEAST (expr [, expr2 [, expr3. ..]
-- Returns the smallest value of all expressions
NVL (expr1, expr2
-- If expr1 is not null, it is returned, otherwise expr2 is returned
SQLCODE
-- Returns SQL error code of last error. Can not be used directly in query,
-- Value must be set to local variable first
SQLERRM
-- Returns SQL error message of last error. Can 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
-- Useful in selecting information from low level sys tables
USER
-- Returns the user name of the user you are logged on
USERENV ('option ')
-- Returns information about the user you are logged on
-- 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 (dt1, dt2) -- returns fractional value of months between dt1, dt2
NEW_TIME (dt, tz1, tz2) -- dt = date in time zone 1, returns date in time zone 2
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 (num2, num1) -- logarithm base num2 of num1
MOD (num2, num1) -- remainder of num2/num1
POWER (num2, num1) -- num2 raised to the num1 power
ROUND (num1 [, num2] -- num1 rounded to num2 decimel places (default 0)
SIGN (num) -- sign of num * 1, 0 if num = 0
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 (num1 [, num2] -- truncate num1 to num2 decimel places (default 0)
 
**************************************** ***************************************
* String Functions, String Result :*
**************************************** ***************************************
(Num) -- ASCII character for num
CHR (num) -- ASCII character for num
CONCAT (str1, str2) -- str1 concatenated with str2 (same as str1 | str2)
INITCAP (str) -- capitalize first letter of each word in str
LOWER (str) -- str with all letters in lowercase
LPAD (str1, num [, str2]) -- left pad str1 to length num with str2 (default spaces)
LTRIM (str [, set]) -- remove set from left side of str (default spaces)
NLS_INITCAP (str [, nls_val]) -- same as initcap for different ages
NLS_LOWER (str [, nls_val]) -- same as lower for different ages
REPLACE (str1, str2 [, str3]) -- replaces str2 with str3 in str1
-- Deletes str2 from str1 if str3 is omitted
RPAD (str1, num [, str2]) -- right pad str1 to length num with str2 (default spaces)
RTRIM (str [, set]) -- remove set from right side of str (default spaces)
SOUNDEX (str) -- phonetic representation of str
SUBSTR (str, num2 [, num1]) -- substring of str, starting with num2,
-- Num1 characters (to end of str if num1 is omitted)
SUBSTRB (str, num2 [, num1]) -- same as substr but num1, num2 expressed in bytes
TRANSLATE (str, set1, set2) -- replaces set1 in str with set2
-- If set2 is longer than set1, it will be truncated
UPPER (str) -- str with all letters in uppercase
 
**************************************** ***************************************
* String Functions, Numeric Result :*
**************************************** ***************************************
 
ASCII (str) -- ASCII value of str
INSTR (str1, str2 [, num1 [, num2]) -- position of num2th occurrence
-- Str2 in str1, starting at num1
-- (Num1, num2 default to 1)
Using B (str1, str2 [, num1 [num2]) -- same as instr, byte values for num1, num2
LENGTH (str) -- number of characters in str
LENGTHB (str) -- number of bytes in str
NLSSORT (str [, nls_val]) -- nls_val byte value of str
 
**************************************** ***************************************
* SQL Conversion Functions *
**************************************** ***************************************
CHARTOROWID (str) -- converts str to ROWID
CONVERT (str, chr_set2 [, chr_set1]) -- converts str to chr_set2
-- Chr_set1 default is the datbase character set
HEXTORAW (str) -- converts hex string value to internal raw values
RAWTOHEX (raw_val) -- converts raw hex value to hex string value
ROWIDTOCHAR (rowid) -- converts rowid to 18 character string format
TO_CHAR (expr [, fmt]) -- converts expr (date or number) to format specified by fmt
TO_DATE (str [, fmt]) -- converts string to date
TO_MULTI_BYTE (str) -- converts single byte string to multi byte string
TO_NUMBER (str [, fmt]) -- converts str to a number formatted by fmt
TO_SINGLE_BYTE (str) -- converts multi byte string to single byte string
 
**************************************** ***************************************
* SQL Date Formats *
**************************************** ***************************************
 
BC, B .C. BC indicator
AD, A.D. AD indicator
CC, SCC Century Code (SCC between des space or-sign)
YYYY, SYYYY 4 digit year (SYYYY between des space or-sign)
IYYY 4 digit ISO year
Y, YYY 4 digit year with comma
YYY, YY, or Y last 3, 2, or 1 digit of year
YEAR, SYEAR year spelled out (SYEAR includes space or-sign)
RR last 2 digits of year in prior or next century
Q quarter or year, 1 to 4
MM month-from 01 to 12
MONTH month spelled out
MON month 3 letter abbreviation
RM roman numeral for month
WW week of year, 1 to 53
Iw iso week of year, 1 to 52 or 1 to 53
W week of month, 1 to 5 (week 1 begins 1st day of the month)
D day of week, 1 to 7
DD day of month, 1 to 31
DDD day of year, one to 366
DAY of week spelled out, nine characters right padded
DY day abbreviation
J # of days since Jan 1, 4712 BC
HH, HH12 hour of day, 1 to 12
HH24 hour of day, 0 to 23
MI minute of hour, 0 to 59
SS second of minute, 0 to 59
SSSSS seconds past midnight, 0-86399
AM, A.M. am indicator
PM, P. M. pm indicator
Any puctuation punctuation between format items, as in 'dd/MM/yy'
Any text between format items
TH converts 1 to '1st', 2 to '2nd ', and so on
SP converts 1 to 'one', 2 to 'two', and so on
SPTH converts 1 to 'first', 2 to 'second', and so on
FX fill exact: uses exact pattern matching
FM fill mode: toggles suppression of blanks in output

Author "svsesunyuan"