Oracle 类型转换参考

常用类型转换举例

-- 日期转字符串 
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
-- 字符串转日期
select to_date('2012-12-13','yyyy-mm-dd') from dual
-- 数字转字符串
select to_char(123.12,'fm9999990.999') from dual 

表 1-1. 格式化函数

函数	返回	描述	例子
to_char(timestamp, text)	text	把 timestamp 转换成 string	to_char(systimestamp,'HH12:MI:SS')
to_char(numeric, text)	text	把 numeric 转换成 string	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把 string 转换成 date	to_date('2012-11-20','yyyy-MM-dd')
to_timestamp(text, text)	date	把 string 转换成 timestamp	to_timestamp('2012-11-20','yyyy-MM-dd')
to_number(text, text)	numeric	把 string 转换成 numeric	to_number('12,454.8-', '99G999D9S')

表 1-2. 用于 date/time 转换的模板

模板	描述
HH	一天的小时数 (01-12)
HH12	一天的小时数 (01-12)
HH24	一天的小时数 (00-23)
MI	分钟 (00-59)
SS	秒 (00-59)
SSSS	午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.	正午标识（大写）
am or a.m. or pm or p.m.	正午标识（小写）
Y,YYY	带逗号的年（4 和更多位）
YYYY	年（4和更多位）
YYY	年的后三位
YY	年的后两位
Y	年的最后一位
BC or B.C. or AD or A.D.	年标识（大写）
bc or b.c. or ad or a.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）－小写

表 1-3 用于 to_char(numeric, text) 的模板

格式	简例	说明
,(逗号)	'9999,999'	逗号,一般以千分位出现,作为分组符号使用.如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定. 变态的例子是 to_char(1234,'9,9,9,9').  注意事项:只能出现在整数部分.
.(点号)	'99.99'	点号,不要念为"句号",句号是个圆圈,点好只能出现在小数点对应的地方.只能出现一次. to_char(1234.34,'9,9,9,9.99') 注意事项:只能出现在一个地方,就是原来数据小数点位置
$(美元符号)	'$999.99'	美元.其实你可以放在任意地方(在10G下) to_char(1234.34,'9,9,9,9.$99')  注意事项:只能出现一次.
0(零)	'0999.99'	零.在对应位置返回对应的字符,如果没有则以'0'填充. to_char(0.34,'9,9,9,0.$99')='$0.34';to_char(1234,'9999.00')='1234.00'; 注意事项:这是一个强制的符号,对应位没有,则以'o'填充,这是9很大不同地方
9	'999.99'	9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符. to_char(123,'999.99')=123.00; TO_CHAR(123,'99999.9')=123.0; 注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回'#'. 譬如to_char(12345,'9999')='#####'
B(空格符)	'B999'	没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置. 'S'||TO_CHAR(1234,'99B99')='S 1234'; 注意事项:只能出现在整数部位.
C(国际货币符号)	'C9999'	在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值)  TO_CHAR(1233,'C9999')='CNY1234' ,这是新的国际标准RMB,关于这个可查询"国际货币符号" 注意事项:只能出现在整数部位第一位. 可以通过alter session set NLS_ISO_CURRENCY='JAPAN';来修改当前会话的设置.
D(ISO 小数位符号)	'999D99'	这是"点号"的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号. 注意事项:没有特别需要一般不要用这个格式符号.也不要轻易修改参数值. 也可用alter sesssion set 来修改. alter session set nls_numeric_characters='!,';   to_char(1234.34,'9999d99')=1234!34
EEEE(科学计算符)	9.9EEEE	科学计算符号 TO_CHAR(2008032001,'9.9EEEE')='2.01E+09',由于是科学计算方法,所以小数位前面加一个9或者0即可,多个是没有意义的.
G(分组符号)	999G999	是逗号(,)的的ISO标准,作为分组符号使用,可以放在多个地方使用. TO_CHAR(123456,'999G9G99')=123,4,56 注意事项:同第八项 -D, 此外如果要转换出小数点,则要和D配合使用,不能和点号配合.
L(本地货币符号)	'L999'	是C的本地版本.可以放在整个格式的最前面和最后面. TO_CHAR(123456,'999G9G99D00L')=123,4,56.00￥ 注意事项:同第七项 C
MI(负号)	'9999MI'	如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格 to_char(1234,'9999mi')||'S'||TO_CHAR(-5678,'9999MI') =1234 S5678- 注意事项:只能放在格式尾巴
PR(符号)	9999PR	是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来. TO_CHAR(-1234.89,'9G999D00PR')=<1,234.89> 注意事项:同12
RN(rn)	RN(rn)	把整数(1-3999)转换为罗马字符.RN表示转为大写,rn表示小写的. declare   i int; begin    for i in 1..20 loop      dbms_output.put_line(to_char(i,'RN'));    end  loop; end; 注意事项:只能自己使用,不能和其它符号组合使用.
S	'9999S'	是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后. TO_CHAR(-1234,'S9999')=-1234;TO_CHAR(1234,'S9999')=+1234
TM	TM9/TMe	使用这个参数等于没有用参数to_char(number)一样,应为'tm9'是默认的格式参数. to_char(1234,'tme')=1234 注意事项:格式要么是TM9,要么是TME. 当数字长度超过64位时候,TM9的输出等同于TME的输出.
U	U999	双币符号,例如欧元.作用同11的L TO_CHAR(999,'U999')=￥999 注意事项：通过NLS_DUAL_CURRENCY 控制
V	999V9	这是个比较古怪，又不是很常使用的符号。它的作用在于做一个计算。 例如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的个数不够 注意事项：格式中不能和小数表达写在一起，但是可以混合货币等。
X	xxxx	转换为16进制。 TO_CHAR(100,'XX')= 64 注意事项：数值必须是大于等于0的整数。前面只能和0或者FM组合使用.
		通过以上的例子，我们了解了各种数字的格式。可以说格式太多样，难于记在脑子，最好是作为一个参考存在着. 归类：   数值类：   0,9,   分组类：  (.),(,),D,G  ,其中点好和逗号因为表示不明显，所以用小括号凸显。   货币类：　$,C,L,U   计算转换类：EEEE,RN,V,X     正负符号：MI,PR,S   其它类：B   正统类：TM