* SQL Group Function *
| s (num can be a column or ex
| pression)
|
(null values are ign *
| ored, default between distin
| ct 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 devi
| ation
|
SUM([distinct or all
| ] num) -- sum of values
|
|
VARIANCE([distinct o
| r all] num) -- variance of v
| alues
|
******************************** ***********************
| ************************
|
* Miscellaneaous Functions : *
|
|
******************** ***************
| ****************************
| ****************
|
DECODE(expr, srch1,
| return1 [,srch2, return2...]
| , default]
|
-- if no search matches t
| he expression then the default is returned,
|
-- otherwise,
| the first search that match
| es will cause
|
-- the corres
| ponding return value to be r
| eturned
|
DUMP(column_name [,fmt [,start_p
| os [, length]]])
|
-- returns an column
| internal oracle format, used
| for getting info about a
|
-- format options : 8 = oc
| tal, 10 = decimel, 16 = hex, 17 = characters
|
-- return type
| codes : 1 = varchar2, 2 = n
| umber, 8 = long, 12 = date,
|
-- 23 = raw,
| 24 = long raw, 69 = rowid,
| 96 = char, 106 = mlslabel
|
GREATEST(expr [,expr2 [, expr3...]] |
-- returns the largest val
| ue of all expressions
|
LEAST(expr [,expr2 [, expr3...]] |
-- returns the
| smallest value of all expre
| ssions
|
-- if expr1 is not null, i
| t is returned, otherwise expr2 is returned
|
-- returns sql error code query,
| of last error. Can not be used directly in
|
-- value must
| be set to local variable fir
| st
|
-- returns sql in query,
| error message of last error
| . Can not be used directly
|
-- value must be set to lo
| cal variable first
|
-- returns the user id of
| the user you are logged on as
|
-- useful in s
| electing information from lo
| w level sys tables
|
-- returns the
| user name of the user you a
| re logged on as
|
-- returns inf
| ormation about the user you
| are logged on as
|
-- options : E
| NTRYID, SESSIONID, TERMINAL,
| LANGUAGE, LABEL, OSDBA
|
-- (
| all options not available in
| all Oracle versions)
|
-- returns the number of b
| ytes used by the expression
|
-- useful in s
| electing information about t
| able space requirements
|
******************** ***************
| ****************************
| ****************
|
* SQL Date Functions (dt represe *
| nts oracle date and time)
|
* (functions return *
| an oracle date unless otherw
| ise 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) -- retu dt2
| rns fractional value of months between dt1,
|
NEW_TIME(dt, tz1, tz zone 2
| 2) -- dt = date in time zo
| ne 1, returns date in time
|
NEXT_DAY(dt, str) -- date etc..)
| of first (str) after dt (str = 'Monday',
|
SYSDATE -- present system date |
ROUND(dt [,fmt] -- roun
| ds dt as specified by format fmt
|
TRUNC(dt [,fmt]
| -- truncates dt as spe
| cified by format fmt
|
******************************** ***********************
| ************************
|
******************************** ***********************
| ************************
|
ABS(num) -- absolute
| value of num
|
CEIL(num) -- smallest integer > or = num |
COS(num) -- cosine(n
| um), num in radians
|
COSH(num)
| -- hyperbolic cosine(num)
|
EXP(num)
| -- e raised to the num powe
| r
|
FLOOR(num) -- largest
| integer < or = num
|
LN(num) -- natural
| logarithm of num
|
LOG(num2, num1) -- logarith
| m base num2 of num1
|
MOD(num2, num1) -- remainde
| r of num2 / num1
|
POWER(num2, num1)
| -- num2 raised to the num1
| power
|
ROUND(num1 [,num2] -- num1 rou
| nded 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 n
| um
|
CONCAT(str1, str2) -- str1
| concatenated with str2 (same as str1||str2)
|
INITCAP(str)
| -- capitalize first lett
| er of each word in str
|
LOWER(str) -- str w
| ith all letters in lowercase
|
LPAD(str1, num [,str2]) -- left spaces)
| pad str1 to length num with str2 (default
|
LTRIM(str [,set])
| -- remove set from left
| side of str (default spaces)
|
NLS_INITCAP(str [,nl
| s_val]) -- same as initcap f
| or different languages
|
NLS_LOWER(str [,nls_
| val]) -- same as lower for
| different languages
|
REPLACE(str1, str2 [,str3]) -- r
| eplaces str2 with str3 in str1
|
--
| deletes str2 from str1 if str3 is omitted
|
RPAD(str1, num [,str (default spaces)
| 2]) -- right pad str1 to
| length num with str2
|
RTRIM(str [,set]) spaces)
| -- remove set from
| right side of str (default
|
SOUNDEX(str)
| -- phonetic represen
| tation of str
|
SUBSTR(str, num2 [,n
| um1]) -- substring of str,
| starting with num2,
|
-- omitted)
| num1 characters (to end of str if num1 is
|
SUBSTRB(str, num2 [, bytes
| num1]) -- same as substr bu
| t num1, num2 expressed in
|
TRANSLATE(str, set1,
| set2) -- replaces set1 in
| str with set2
|
-- truncated
| if set2 is longer than set1, it will be
|
UPPER(str)
| -- str with all lett
| ers in uppercase
|
******************** ***************
| ****************************
| ****************
|
* String Functions, *
| Numeric Result :
|
|
******************************** ***********************
| ************************
|
ASCII(str)
| -- ASCII value of str
|
INSTR(str1, str2 [,num1 [,num2]]
| ) -- position of num2th occurrence of
|
| -- str2 in str1, starting at num1
|
| -- (num1, num2 default to 1)
|
INSTRB(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
|
character set
| -- chr_set1
| default is the datbase
|
HEXTORAW(str)
| -- converts hex string va
| lue to internal raw values
|
RAWTOHEX(raw_val) -- convert
| s raw hex value to hex string value
|
ROWIDTOCHAR(rowid)
| -- converts rowid to 18 ch
| aracter string format
|
TO_CHAR(expr [,fmt]) fmt
| -- converts expr(date or n
| umber) to format specified by
|
TO_DATE(str [,fmt])
| -- converts string to dat
| e
|
TO_MULTI_BYTE(str) -- convert
| s single byte string to multi byte string
|
TO_NUMBER(str [,fmt]) -- convert
| s str to a number formatted by fmt
|
TO_SINGLE_BYTE(str)
| -- converts multi byte st
| ring to single byte string
|
******************************** ***********************
| ************************
|
******************** ***************
| ****************************
| ****************
|
CC, SCC Cent
| ury Code (SCC includes space
| or - sign)
|
YYYY, SYYYY 4 digit year (SY
| YYY includes space or - sign)
|
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 |
MON month 3 letter abbreviation |
RM roman numeral for month |
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)
|
DDD day of year, 1 to 366 |
DAY day of week spel
| led out, nine characters right padded
|
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 seco
| nds past midnight, 0 to 8639
| 9
|
any puctuation punc
| tuation between format items
| , as in 'DD/MM/YY'
|
any text text between format items |
TH conv
| erts 1 to '1st', 2 to '2nd',
| and so on
|
SP converts 1 to 'o
| ne', 2 to 'two', and so on
|
SPTH converts 1 to 'F
| IRST', 2 to 'SECOND', and so on
|
FX fill
| exact : uses exact pattern
| matching
|
FM fill mode : tog
| gles suppression of blanks in output
|
|