Oracle的常用函数
1、字符函数
lower:把字符转成小写
upper:把字符转成大写
initcap:把单词的第一个字母变成大写
concat:连接字符 concat(‘good’,’morning’)=goodmoring
SUBSTR (columnexpression, m[,n]) 用于对字符串进行截取操作,从第m 个位置开始,把其后的连续n个字符的部分截取下来,如果m位负值,则从末尾开始计算。
eg:substr(‘string’,1,3) =str
substr(‘string’-3,3)=ing
INSTR('String', 'r')=3
LPAD(sal,10,'*') =*******sal RPAD()
length: 用于返回表达式中的字符数,注意返回的是NUMBER。
NVL(expression1, expression2)
NVL 函数用以把一个空值转换为一个实值,如:
NVL(100/quantity, 0) ,要是quantity 为空值,该函数返回一个0。
2、数学函数
round:四舍五入
round(2.566,2)=2.27
round(45,-1)=50
trunc: 截断
trunc(2.566,2)=2.56
trunc(45,-1)=40
mod: mod(m,n)
floor(m/n),ceil(m/n)
3、日期函数:
a) months_between(date1,date2):算date1 和date2 之间的月的数量,可以是小数可以是负数
months_between(’01-sep-95’,’11-jan-94’)=1.9774194
b) add_months(date,n):为date 加上N 个月,N 只可以是整数
c) next_date(date,’char’):查找date 的下一个星期Nnext_date(’01-sep-95’,’FRIDAY’)=08-SEP-95
d) last_day(date):查找date 月的最后一天。
e) round(date):把日期四舍五入
f) round(25-MAY-95’,’MONTH’)=01-JUN-95
g) round(25-MAY-95’,’YEAR’)=01-JAN-95
h) trunc(date):把日期截断
i) trunc (25-MAY-95’,’MONTH’)=01-MAY-95
j) trunc (25-MAY-95’,’YEAR’)=01-JAN-95
4、转换函数
TO_CHAR:
TO_CHAR(date,’fmt’):fm前缀用来去除首尾的空字符或0
TO_CHAR(total,’fm$999999’)
如果想转成$0.25,那就要写成fm$9999990.99
to_char(1234,’09999’)
可以把日期转换成字符
TO_CHAR(log_time,’MM/YY’)
SELECT to_char(SYSDATE,'yyyy"年"mm"月"dd"日"') FROM dual
具体格式如下
Number Format Elements Results of Number Conversionshttp://xsb.itpub.net/post/419/31722
Element | Example | Description |
---|---|---|
, (comma) |
| Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
. (period) |
| Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model. |
$ |
| Returns value with a leading dollar sign. |
0 |
| Returns leading zeros. Returns trailing zeros. |
9 |
| Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. |
B |
| Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). |
C |
| Returns in the specified position the ISO currency symbol (the current value of the |
D |
| Returns in the specified position the decimal character, which is the current value of the Restriction: You can specify only one decimal character in a number format model. |
EEEE |
| Returns a value using in scientific notation. |
G |
| Returns in the specified position the group separator (the current value of the Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. |
L |
| Returns in the specified position the local currency symbol (the current value of the |
MI |
| Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model. |
PR |
| Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restriction: The PR format element can appear only in the last position of a number format model. |
RN rn |
| Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
S |
| Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+). Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model. |
TM |
| The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation. Restrictions:
|
U |
| Returns in the specified position the Euro (or other) dual currency symbol (the current value of the |
V |
| Returns a value multiplied by 10n (and if necessary, round it up), where |
X |
| Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer. Restrictions:
|
shows the results of the following query for different values of number
and 'fmt'
:
SELECT TO_CHAR(number, 'fmt')
FROM DUAL;
number | 'fmt' | Result |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Datetime Format Elements
Element | Specify in TO_* datetime functions? | Description |
---|---|---|
-
/
,
.
;
:
"text"
| Yes | Punctuation and quoted text is reproduced in the result. |
AD
A.D.
| Yes | AD indicator with or without periods. |
AM
A.M.
| Yes | Meridian indicator with or without periods. |
BC
B.C.
| Yes | BC indicator with or without periods. |
CC
SCC
| No | Century.
For example, 2002 returns 21; 2000 returns 20. |
D
| Yes | Day of week (1-7). |
DAY
| Yes | Name of day, padded with blanks to display width of the widest name of day in the date language used for this element. |
DD
| Yes | Day of month (1-31). |
DDD
| Yes | Day of year (1-366). |
DL
| Yes | Returns a value in the long date format, which is an extension of Oracle Database's Restriction: You can specify this format only with the |
DS
| Yes | Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the Restriction: You can specify this format only with the |
DY
| Yes | Abbreviated name of day. |
E
| No | Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE
| No | Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
FF [1..9]
| Yes | Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime datatype or the datatype's default precision. Examples:
|
FM
| Yes | Returns a value with no leading or trailing blanks. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference |
FX
| Yes | Requires exact matching between the character data and the format model. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference |
HH
| Yes | Hour of day (1-12). |
HH12
| No | Hour of day (1-12). |
HH24
| Yes | Hour of day (0-23). |
IW
| No | Week of year (1-52 or 1-53) based on the ISO standard. |
IYY
IY
I
| No | Last 3, 2, or 1 digit(s) of ISO year. |
IYYY
| No | 4-digit year based on the ISO standard. |
J
| Yes | Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI
| Yes | Minute (0-59). |
MM
| Yes | Month (01-12; January = 01). |
MON
| Yes | Abbreviated name of month. |
MONTH
| Yes | Name of month, padded with blanks to display width of the widest name of month in the date language used for this element. |
PM
P.M.
| No | Meridian indicator with or without periods. |
Q
| No | Quarter of year (1, 2, 3, 4; January - March = 1). |
RM
| Yes | Roman numeral month (I-XII; January = I). |
RR
| Yes | Lets you store 20th century dates in the 21st century using only two digits. See Also: Additional discussion on |
RRRR
| Yes | Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year. |
SS
| Yes | Second (0-59). |
SSSSS
| Yes | Seconds past midnight (0-86399). |
TS
|
| Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the Restriction: You can specify this format only with the |
TZD
| Yes | Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. Example: |
TZH
| Yes | Time zone hour. (See Example: |
TZM
| Yes | Time zone minute. (See Example: |
TZR
| Yes | Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific |
WW
| No | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
W
| No | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
X
| Yes | Local radix character. Example: |
Y,YYY
| Yes | Year with comma in this position. |
YEAR
SYEAR
| No | Year, spelled out; |
YYYY
SYYYY
| Yes | 4-digit year; |
YYY
YY
Y
| Yes | Last 3, 2, or 1 digit(s) of year. |