Home > 我就是未分类 > oracle 常用函数

oracle 常用函数

August 11th, 2005

* 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
— returns sql error code of last error.  Can not be used directly in query,
— value must be set to local variable first
— returns sql error message of last error.  Can not be used directly in query,
— value must be set to local variable first
— returns the user id of the user you are logged on as
— useful in selecting information from low level sys tables
— returns the user name of the user you are logged on as
— returns information about the user you are logged on as
—           (all options not available in all Oracle versions)
— 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 languages
NLS_LOWER(str [,nls_val])   — same as lower for different languages
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 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
— 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 includes space or – sign)
YYYY, SYYYY     4 digit year (SYYYY includes 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, 1 to 366
DAY             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 to 86399
AM, A.M.        am indicator
PM, P.M.        pm indicator
any puctuation  punctuation between format items, as in ‘DD/MM/YY’
any text        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


  1. October 15th, 2016 at 18:56 | #1

    I am continuously invstigating online for posts that can help me. Thx!

  1. No trackbacks yet.