Oracle tochar格式化函数.docx
《Oracle tochar格式化函数.docx》由会员分享，可在线阅读，更多相关《Oracle tochar格式化函数.docx（15页珍藏版）》请在冰点文库上搜索。
Oracletochar格式化函数
Oracleto_char格式化函数
Postgres 格式化函数提供一套有效的工具用于把各种数据类型（日期/时间，int，float，numeric）转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。
注意：
所有格式化函数的第二个参数是用于转换的模板。
表5-7.格式化函数
函数
返回
描述
例子
to_char（timestamp,text）
text
把timestamp转换成string
to_char（timestamp'now','HH12:
MI:
SS'）
to_char（int,text）
text
把int4/int8转换成string
to_char（125,'999'）
to_char（float,text）
text
把float4/float8转换成string
to_char（125.8,'999D9'）
to_char（numeric,text）
text
把numeric转换成string
to_char（numeric'-125.8','999D99S'）
to_date（text,text）
date
把string转换成date
to_date（'05Dec2000','DDMonYYYY'）
to_timestamp（text,text）
date
把string转换成timestamp
to_timestamp（'05Dec2000','DDMonYYYY'）
to_number（text,text）
numeric
把string转换成numeric
to_number（'12,454.8-','99G999D9S'）
表5-8.用于date/time转换的模板
模板
描述
HH
一天的小时数（01-12）
HH12
一天的小时数（01-12）
HH24
一天的小时数（00-23）
MI
分钟（00-59）
SS
秒（00-59）
SSSS
午夜后的秒（0-86399）
AMorA.M.orPMorP.M.
正午标识（大写）
amora.m.orpmorp.m.
正午标识（小写）
Y,YYY
带逗号的年（4和更多位）
YYYY
年（4和更多位）
YYY
年的后三位
YY
年的后两位
Y
年的最后一位
BCorB.C.orADorA.D.
年标识（大写）
bcorb.c.oradora.d.
年标识（小写）
MONTH
全长大写月份名（9字符）
Month
全长混合大小写月份名（9字符）
month
全长小写月份名（9字符）
MON
大写缩写月份名（3字符）
Mon
缩写混合大小写月份名（3字符）
mon
小写缩写月份名（3字符）
MM
月份（01-12）
DAY
全长大写日期名（9字符）
Day
全长混合大小写日期名（9字符）
day
全长小写日期名（9字符）
DY
缩写大写日期名（3字符）
Dy
缩写混合大小写日期名（3字符）
dy
缩写小写日期名（3字符）
DDD
一年里的日子（001-366）
DD
一个月里的日子（01-31）
D
一周里的日子（1-7；SUN=1）
W
一个月里的周数
WW
一年里的周数
CC
世纪（2位）
J
Julian日期（自公元前4712年1月1日来的日期）
Q
季度
RM
罗马数字的月份（I-XII；I=JAN）－大写
rm
罗马数字的月份（I-XII；I=JAN）－小写
所有模板都都允许使用前缀和后缀修改器。
模板里总是允许使用修改器。
前缀 'FX'只是一个全局修改器。
表5-9.用于日期/时间模板 to_char（）的后缀
后缀
描述
例子
FM
填充模式前缀
FMMonth
TH
大写顺序数后缀
DDTH
th
小写顺序数后缀
DDTH
FX
固定模式全局选项（见下面）
FXMonthDDDay
SP
拼写模式（还未实现）
DDSP
用法须知：
∙如果没有使用 FX 选项，to_timestamp 和 to_date 忽略空白。
FX 必须做为模板里的第一个条目声明。
∙反斜杠（"\"）必须用做双反斜杠（"\\"），例如'\\HH\\MI\\SS'。
∙双引号（'"'）之间的字串被忽略并且不被分析。
如果你想向输出写双引号，你必须在双引号前面放置一个双反斜杠（'\\'），例如'\\"YYYYMonth\\"'。
∙to_char 支持不带前导双引号（'"'）的文本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例如：
'"HelloYear:
"YYYY'）。
表5-10.用于 to_char（numeric）的模板
模板
描述
9
带有指定位数的值
0
前导零的值
.（句点）
小数点
（逗号）
分组（千）分隔符
PR
尖括号内负值
S
带负号的负值（使用本地化）
L
货币符号（使用本地化）
D
小数点（使用本地化）
G
分组分隔符（使用本地化）
MI
在指明的位置的负号（如果数字<0）
PL
在指明的位置的正号（如果数字>0）
SG
在指明的位置的正/负号
RN
罗马数字（输入在1和3999之间）
THorth
转换成序数
V
移动 n 位（小数）（参阅注解）
EEEE
科学记数。
现在不支持。
用法须知：
∙使用'SG'，'PL'或'MI'的带符号字并不附着在数字上面；例如，to_char（-12,'S9999'）生成'-12'，而to_char（-12,'MI9999'）生成'-12'。
Oracle 里的实现不允许在9 前面使用 MI，而是要求 9 在 MI 前面。
∙PL，SG，和 TH 是 Postgres 扩展。
∙9 表明一个与在 9 字串里面的一样的数字位数。
如果没有可用的数字，那么使用一个空白（空格）。
∙TH 不转换小于零的值，也不转换小数。
TH 是一个 Postgres 扩展。
∙V 方便地把输入值乘以 10^n，这里 n 是跟在 V 后面的数字。
to_char 不支持把V 与一个小数点绑在一起使用（例如."99.9V99"是不允许的）。
表5-11. to_char 例子
输入
输出
to_char（now（）,'Day,HH12:
MI:
SS'）
'Tuesday,05:
39:
18'
to_char（now（）,'FMDay,HH12:
MI:
SS'）
'Tuesday,05:
39:
18'
to_char（-0.1,'99.99'）
'-.10'
to_char（-0.1,'FM9.99'）
'-.1'
to_char（0.1,'0.9'）
'0.1'
to_char（12,'9990999.9'）
'0012.0'
to_char（12,'FM9990999.9'）
'0012'
to_char（485,'999'）
'485'
to_char（-485,'999'）
'-485'
to_char（485,'999'）
'485'
to_char（1485,'9,999'）
'1,485'
to_char（1485,'9G999'）
'1485'
to_char（148.5,'999.999'）
'148.500'
to_char（148.5,'999D999'）
'148,500'
to_char（3148.5,'9G999D999'）
'3148,500'
to_char（-485,'999S'）
'485-'
to_char（-485,'999MI'）
'485-'
to_char（485,'999MI'）
'485'
to_char（485,'PL999'）
'+485'
to_char（485,'SG999'）
'+485'
to_char（-485,'SG999'）
'-485'
to_char（-485,'9SG99'）
'4-85'
to_char（-485,'999PR'）
'<485>'
to_char（485,'L999'）
'DM485
to_char（485,'RN'）
'CDLXXXV'
to_char（485,'FMRN'）
'CDLXXXV'
to_char（5.2,'FMRN'）
V
to_char（482,'999th'）
'482nd'
to_char（485,'"Goodnumber:
"999'）
'Goodnumber:
485'
to_char（485.8,'"Pre-decimal:
"999"Post-decimal:
".999'）
'Pre-decimal:
485Post-decimal:
.800'
to_char（12,'99V999'）
'12000'
to_char（12.4,'99V999'）
'12400'
to_char（12.45,'99V9'）
'125'
补充：
一，TO_CHAR（NUMBER）
本函数把参数N转为一个VARCHAR2类型的数值。
N可以是NUMBER,BINARY_FLOAT,或者BINARY_DOUBLE。
如果不带格式，那么函数会把N转换为足以表示N的VARCHAR2字符串。
格式表参考：
序号
格式简例说明
1 ,（逗号）'9999,999'逗号,一般以千分位出现,作为分组符号使用.如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定.
变态的例子是to_char（1234,'9,9,9,9'）.
注意事项:
只能出现在整数部分.
2.（点号）'99.99'点号,不要念为"句号",句号是个圆圈,点好只能出现在小数点对应的地方.只能出现一次.
to_char（1234.34,'9,9,9,9.99'）
注意事项:
只能出现在一个地方,就是原来数据小数点位置
3$（美元符号）'$999.99'美元.其实你可以放在任意地方（在10G下）
to_char（1234.34,'9,9,9,9.$99'）
注意事项:
只能出现一次.
40（零）'0999.99'
零.在对应位置返回对应的字符,如果没有则以'0'填充.
to_char（0.34,'9,9,9,0.$99'）='$0.34';to_char（1234,'9999.00'）='1234.00';
注意事项:
这是一个强制的符号,对应位没有,则以'o'填充,这是9很大不同地方
59'999.99'
9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符.
to_char（123,'999.99'）=123.00;TO_CHAR（123,'99999.9'）=123.0;
注意事项:
对于0和9而言,如果格式的位数不如数字的位数多,会返回'#'.
譬如to_char（12345,'9999'）='#####'
6B（空格符）'B999'没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置.
'S'||TO_CHAR（1234,'99B99'）='S1234';
注意事项:
只能出现在整数部位.
7C（国际货币符号）'C9999'在特定的位置返回一个ISO货币符号（就是NLS_ISO_CURRENCY参数所代表的值）
TO_CHAR（1233,'C9999'）='CNY1234',这是新的国际标准RMB,关于这个可查询"国际货币符号"
注意事项:
只能出现在整数部位第一位.
可以通过altersessionsetNLS_ISO_CURRENCY='JAPAN';来修改当前会话的设置.
8D（ISO小数位符号）'999D99'
这是"点号"的国际版本（ISO）,作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.
注意事项:
没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.
也可用altersesssionset来修改.
altersessionsetnls_numeric_characters='!
'; to_char（1234.34,'9999d99'）=1234!
34
9EEEE（科学计算符）9.9EEEE科学计算符号
TO_CHAR（2008032001,'9.9EEEE'）='2.01E+09',由于是科学计算方法,所以小数位前面加一个9或者0即可,多个是没有意义的.
10G（分组符号）999G999是逗号（,）的的ISO标准,作为分组符号使用,可以放在多个地方使用.
TO_CHAR（123456,'999G9G99'）=123,4,56
注意事项:
同第八项-D,此外如果要转换出小数点,则要和D配合使用,不能和点号配合.
11L（本地货币符号）'L999'是C的本地版本.可以放在整个格式的最前面和最后面.
TO_CHAR（123456,'999G9G99D00L'）=123,4,56.00￥
注意事项:
同第七项C
12MI（负号）'9999MI'
如果是负数,在尾部加上负号（-）,如果是正数,则尾巴加上空格
to_char（1234,'9999mi'）||'S'||TO_CHAR（-5678,'9999MI'）=1234S5678-
注意事项:
只能放在格式尾巴
13PR（符号）9999PR是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来.
TO_CHAR（-1234.89,'9G999D00PR'）=<1,234.89>
注意事项:
同12
14RN（rn）RN（rn）把整数（1-3999）转换为罗马字符.RN表示转为大写,rn表示小写的.
declare
iint;
begin
foriin1..20loop
dbms_output.put_line（to_char（i,'RN'））;
endloop;
end;
注意事项:
只能自己使用,不能和其它符号组合使用.
15S'9999S'是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后.
TO_CHAR（-1234,'S9999'）=-1234;TO_CHAR（1234,'S9999'）=+1234
16TMTM9/TMe
使用这个参数等于没有用参数to_char（number）一样,应为'tm9'是默认的格式参数.
to_char（1234,'tme'）=1234
注意事项:
格式要么是TM9,要么是TME.
当数字长度超过64位时候,TM9的输出等同于TME的输出.
17UU999双币符号,例如欧元.作用同11的L
TO_CHAR（999,'U999'）=￥999
注意事项：
通过NLS_DUAL_CURRENCY控制
18V999V9这是个比较古怪，又不是很常使用的符号。
它的作用在于做一个计算。
例如TO_CHAR（N,'999V9'）,以p表示V的位置,则该表达式＝to_char（N×（10的P-1次方））.但是9个数又必须保证大于等于乘积之后表示的位数.
TO_CHAR（5,'9V'）=5*1=5;
TO_CHAR（5,'9V9'）=5*10=50
TO_CHAR（5,'9V99'）=500
TO_CHAR（50,'9V99'）='######'9的个数不够
注意事项：
格式中不能和小数表达写在一起，但是可以混合货币等。
19Xxxxx转换为16进制。
TO_CHAR（100,'XX'）=64
注意事项：
数值必须是大于等于0的整数。
前面只能和0或者FM组合使用.
20 通过以上的例子，我们了解了各种数字的格式。
可以说格式太多样，难于记在脑子，最好是作为一个参考存在着.
归类：
数值类：
0,9,
分组类：
（.）,（,）,D,G,其中点好和逗号因为表示不明显，所以用小括号凸显。
货币类：
$,C,L,U
计算转换类：
EEEE,RN,V,X
正负符号：
MI,PR,S
其它类：
B
正统类：
TM
1.2格式说明
从上图可以看到格式是可选取的，保留字fmt也不是必须的，关键是NLSPARAM的意思，
从第2-54章节（FORMATMODELS）查看。
NLSPARAM可以是这样 'NLS_NUMERIC_CHARACTERS=''dg''NLS_CURRENCY=''text''NLS_ISO_CURRENCY=territory'
举例：
（待续）
SELECTTO_CHAR（-10000,'C99G999D99PR',
'NLS_NUMERIC_CHARACTERS=''._''NLS_ISO_CURRENCY=''UNITEDKINGDOM'''）"Amount"
FROMDUAL;
结果返回：
如果要了解可以使用的NLS_ISO_CURRENCY值,可以查询数据库的视图V_$NLS_VALID_VALUES
二，TO_CHAR（CHARACTER）
把NCLOB,CLOB,NCHAR转换为VARCHAR2.
三，TO_CHAR（DATETIME）
把日期转化为字符串.
关于这个格式，没有什么太好说的。
它的格式主要分为两类：
简写单个字母（或者其复现形式）代表时间位置譬如yyyy,mm,dd,hh之类;其次是以英文的时间单词的简写代表时间，例如mon,day,year.
下表的格式基本上也都可以用于TO_DATE,TO_TIMESTAMP,TO_TIMESTAMP_TZ,TO_YMINTERVAL,
TO_DSINTERVAL函数。
序号
格式
简例
说明
1-/,.;:
"text"略时间分隔符号，除了标准的几个，还允许用文字作为分割符号。
例如to_char（sysdate,'YYYY"年"mm"月"dd"日"'）=2008年04月24日
2AD
A.D. 即拉丁文AnnoDomini的简写，表示公元.会根据nls的不同转换为公元或者ad等
无特殊注意事项
3AM
A.M. 上午的简写,同pm,p.m.（下午）,中文环境输出为上午（如果是上午）
4BC
B.C. 虽然标准的写法是B.c.（c小写）或者BC,好在Oracle不讲究这个。
表示公元前
5CC
SCC 返回世纪，以阿拉伯数字表示
如果年的后两位介于01-99那么，返回前两位+1，否则返回前两位
6D 一周之中的某天，返回的是序号1-7
7DAY 一周之中的某天，不过返回的是星期几而已，这和语言设置有关系，在中国环境NLS_DATE_LANGUAGE=SIMPLIFIEDCHINESE，用星期一到星期天表示
8DD 月份中的某天（1-31）
9DDD 年份中的某天（1-366）
10DL'DL'返回长的日期格式。
受到NLS_TERRITORY,NLS_LANGUAGE参数控制。
例2008年4月28日星期一
限制：
除了DL，其它什么的都不能设置。
11DS 返回短的日期格式。
受到NLS_TERRITORY,NLS_LANGUAGE参数控制。
例如2008-04-28
限制：
除了DL，其它什么的都不能设置。
12DY 日期的简称，就是星期几（当然这指的是中国环境下）
13E 纪元简称，但是只适合以下集中日历：
日本皇室，中华民国,太过佛历
14EE 纪元全程，适合情况同E
15FF[1..9] 就是毫秒,如果不更上数字就是用默认的精度。
只能用于timestamp类型的。
16FM 值得注意的一个函数：
不返回任何内容。
有点不明白oracle为什么设置这个东西.
17FX 同上
18HH 表示小时，为12小时制，同hh12（1-12）
19HH12 表示小时，为12小时制（1-12）
20HH24 表示小时，为24小时制（0-23）
21IW ISO标准的星期序号（1-52,或者1-53）
22IYYY
IYY
IY
I
IYY,IY,I,ISO年（4位）的4,3，2，1位数字（倒数）
to_char（to_date（21120401,'yyyymmdd'）,'iyyy,iyy,iy,i'）=2112,112,12,2
23J 儒略日（多用于天文的一种日历）,从公元前4712年一月一日算起，得出的结果是个整数，算法大体为（公元日期+4712）*儒略日历年平均天数
24MI 秒（0-59）
25MM 2位月（1-12）
26MON 月的简称，和国家有关系NLS_DATE_LANGUAGE，例如04在中文环境下用4月表示.
27MONTH 月的名称，国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。
28PM
P.M.
同am,a.m.表示下午
29Q 季度（1-4）
30RM 用罗马数字表示的月份，I ,II,III,IV,V ,VI,VII,VIII,IX,X ,XI,XII
31RR 有点四舍五入表示年的意思，具体的用法有那么一点点复杂。
以s表示输入的年份最后两位，c表示当前的年份最后两位,其输出结果（新的年份前两位）可以用函数r=f（s,c）来表示,s2,c2分别表示s,c的前两位。
1）s=[0,49],c=[0,49],则r=c2
2）s=[0,49],c=[50,99],则r=c2+1
3）s=[50,99],c=[0,49],则r=c2-1
4）s=[50,99],c=[50,99],则r=c2
升级会员 