Oracleto_char格式化函数剖析

1、Oracle to_char 格式化函数Postgres格式化函数提供一套有效的工具用于把各种数据类型（日期/时间， int ， float ，numeric ）转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。注意： 所有格式化函数的第二个参数是用于转换的模板。表 5-7.格式化函数函数返回描述例子to_char (timestamp,text把 timestamp转to_char (timestamptext)换成 string'now','HH12:MI:SS')to_char (int, text)text把 int4/int8转 to

2、_char (125, '999')换成 stringto_char (float,text把to_char (125.8, '999D9')text)float4/float8转换成 stringto_char (numeric,text把 numeric转换 to_char (numeric '-125.8',text)成 string'999D99S')to_date(text, text)date把 string转换to_date('05 Dec 2000', 'DD成 dateMon YYYY&#

3、39;)to_timestamp(text,date把 string转换to_timestamp('05 Dectext)成 timestamp2000', 'DD Mon YYYY')to_number(text,numeric把 string转换to_number('12,454.8-',text)成 numeric'99G999D9S')表 5-8. 用于 date/time 转换的模板模板描述HHHH12HH24一天的小时数一天的小时数一天的小时数(01-12)(01-12)(00-23)MI分钟(00-59)SS秒(00

4、-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小

5、写缩写月份名（ 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位）JJulian日期（自公元前4712 年 1 月 1 日来的日期）Q季度RM罗马数字的月份（ I-XII；I=JAN）大写rm罗马数字的月份（ I-XII；I=JAN）小写所有模板都都允

6、许使用前缀和后缀修改器。模板里总是允许使用修改器。前缀'FX' 只是一个全局修改器。表 5-9. 用于日期 /时间模板 to_char () 的后缀后缀 描述例子FM填充模式前缀FMMonthTH大写顺序数后缀DDTHth小写顺序数后缀DDTHFX固定模式全局选项（见下面）FX Month DD DaySP拼写模式（还未实现）DDSP用法须知：如果没有使用 FX选项， to_timestamp 和 to_date忽略空白。 FX必须做为模板里的第一个条目声明。反斜杠（ ""）必须用做双反斜杠（"" ），例如 'HHMISS'

7、; 。双引号（ '"'）之间的字串被忽略并且不被分析。如果你想向输出写双引号，你必须在双引号前面放置一个双反斜杠（'' ），例如 '"YYYY Month"'。to_char 支持不带前导双引号（'"' ）的文本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例如：'"Hello Year: "YYYY'）。表 5-10. 用于 to_char (numeric ) 的模板模板描述9带有指定位数的值0前导零的值.（句点） 小数点

8、, （逗号） 分组（千）分隔符PR尖括号内负值S带负号的负值（使用本地化）L货币符号（使用本地化）D小数点（使用本地化）G分组分隔符（使用本地化）MI在指明的位置的负号（如果数字< 0 ）PL在指明的位置的正号（如果数字> 0 ）SG在指明的位置的正 / 负号RN罗马数字（输入在 1 和 3999之间）TH or th转换成序数V移动n位（小数）（参阅注解）EEEE 科学记数。现在不支持。用法须知：使用'SG' ， 'PL'或'MI'的带符号字并不附着在数字上面；例如，to_char (-12,'S9999')生成 &

9、#39; -12'，而to_char (-12, 'MI9999')生成 '- 12'。 Oracle里的实现不允许在9 前面使用MI，而是要求9 在MI 前面。PL，SG，和 TH是 Postgres 扩展。9 表明一个与在9 字串里面的一样的数字位数。如果没有可用的数字，那么使用一个空白（空格）。TH不转换小于零的值，也不转换小数 。 TH是一个Postgres扩展。V 方便地把输入值乘以10n，这里n 是跟在V 后面的数字。 to_char不支持把V 与一个 小数 点绑在一起使用（例如. "99.9V99"是不允许的）。表 5-

10、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,&

11、#39;9990999.9')' 0012.0'to_char (12,'FM9990999.9')'0012'to_char (485,'999')' 485'to_char (-485,'999')'-485'to_char (485,'9 9 9')'485'to_char (1485,'9,999')' 1,485'to_char (1485,'9G999')' 1 485'

12、;to_char (148.5,'999.999')' 148.500'to_char (148.5,'999D999')' 148,500'to_char (3148.5,'9G999D999')' 3 148,500'to_char (-485,'999S')'485-'to_char (-485,'999MI')'485-'to_char (485,'999MI')'485'to_char (485

13、,'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')'DM 485to_char (485,'RN')' CDLXXXV'to_c

14、har (485,'FMRN')'CDLXXXV'to_char (5.2,'FMRN')Vto_char (482,'999th')' 482nd'to_char (485, '"Good number:"999')'Good number: 485'to_char (485.8,'"Pre-decimal:"999"'Pre-decimal: 485Post-decimal:" .999')Pos

15、t-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

16、,(逗号 ) '9999,999' 逗号 ,一般以千分位出现 ,作为分组符号使用 .如果需要您也可以当作是十分位 ,百分位出现 ,可以出现 N 次 ,视乎数字的大小而定 .变态的例子是to_char(1234,'9,9,9,9').注意事项 : 只能出现在整数部分.2 .( 点号 ) '99.99'点号 ,不要念为 " 句号 ",句号是个圆圈,点好只能出现在小数点对应的地方. 只能出现一次 .to_char(1234.34,'9,9,9,9.99')注意事项 : 只能出现在一个地方,就是原来数据小数点位置3 $

17、( 美元符号 ) '$999.99'美元 . 其实你可以放在任意地方(在 10G 下 )to_char(1234.34,'9,9,9,9.$99')注意事项 : 只能出现一次 .4 0( 零 ) '0999.99'零 .在对应位置返回对应的字符,如果没有则以 '0' 填充 .to_char(0.34,'9,9,9,0.$99')='$0.34'to_char(1234,'9999.00')='1234.00'注意事项 : 这是一个强制的符号,对应位没有 ,则以 

18、9;o' 填充 ,这是 9 很大不同地方5 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')='#'6 B( 空格符 ) 'B999'没有其它特别作用

19、,在整数部分最前面加一个空格,可以出现在任意位置 .'S'|TO_CHAR(1234,'99B99')='S 1234'注意事项 : 只能出现在整数部位.7 C( 国际货币符号 ) 'C9999'在特定的位置返回一个ISO 货币符号 (就是NLS_ISO_CURRENCY参数所代表的值)TO_CHAR(1233,'C9999')='CNY1234' ,这是新的国际标准RMB, 关于这个可查询"国际货币符号 "注意事项 : 只能出现在整数部位第一位.可以通过 alter sessi

20、on set NLS_ISO_CURRENCY='JAPAN'来修改当前会话的设置.8 D(ISO小数位符号 ) '999D99'这是 "点号 "的国际版本 (ISO), 作用等同于点号,也是只能出现一次.所不同的是 ,数据库会根据 NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.注意事项 : 没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.也可用 alter sesssion set来修改 .alter session set nls_numeric_characters='!,'

21、to_char(1234.34,'9999d99')=1234!349 EEEE( 科学计算符 ) 9.9EEEE科学计算符号TO_CHAR(2008032001,'9.9EEEE')='2.01E+09',由于是科学计算方法, 所以小数位前面加一个 9 或者 0 即可 ,多个是没有意义的.10 G( 分组符号 ) 999G999是逗号 (,) 的的 ISO 标准 ,作为分组符号使用, 可以放在多个地方使用 .TO_CHAR(123456,'999G9G99')=123,4,56注意事项 : 同第八项-D, 此外如果要转换出小数点

22、, 则要和 D 配合使用 ,不能和点号配合.11 L( 本地货币符号) 'L999'是 C 的本地版本 .可以放在整个格式的最前面和最后面.TO_CHAR(123456,'999G9G99D00L')=123,4,56.00￥注意事项 : 同第七项C12 MI( 负号 ) '9999MI'如果是负数 ,在尾部加上负号(-), 如果是正数 ,则尾巴加上空格to_char(1234,'9999mi')|'S'|TO_CHAR(-5678,'9999MI') =1234 S5678-注意事项 : 只能放在

23、格式尾巴13 PR( 符号 ) 9999PR是表达负数的另外一种方式.如果是正数 ,则头部加上空格;如果是负数 ,则用小简括号<>把数字包起来.TO_CHAR(-1234.89,'9G999D00PR')=<1,234.89>注意事项 : 同 1214 RN(rn) RN(rn)把整数 (1-3999) 转换为罗马字符.RN 表示转为大写,rn 表示小写的 .declarei int; beginfor i in 1.20 loopdbms_output.put_line(to_char(i,'RN'); end loop;end;注意事

24、项 : 只能自己使用 ,不能和其它符号组合使用.15 S '9999S'是 12,13 的综合改进版本.为整数加一个正号+,为负数加一个符号-.S 在前则加在前 ,在后则在后 .TO_CHAR(-1234,'S9999')=-1234;TO_CHAR(1234,'S9999')=+123416 TM TM9/TMe使用这个参数等于没有用参数to_char(number)一样 ,应为 'tm9' 是默认的格式参数.to_char(1234,'tme')=1234注意事项 : 格式要么是TM9, 要么是 TME.当数字

25、长度超过64 位时候 ,TM9 的输出等同于TME 的输出 .17 U U999双币符号 , 例如欧元 .作用同 11 的 LTO_CHAR(999,'U999')=￥ 999注意事项：通过NLS_DUAL_CURRENCY控制18 V 999V9这是个比较古怪，又不是很常使用的符号。它的作用在于做一个计算。例如 TO_CHAR(N,'999V9'),以 p 表示 V 的位置 ,则该表达式to_char(N ×(10 的 P-1 次方). 但是 9 个数又必须保证大于等于乘积之后表示的位数.TO_CHAR(5,'9V')=5*1=5;T

26、O_CHAR(5,'9V9')=5*10=50TO_CHAR(5,'9V99')=500TO_CHAR(50,'9V99')='#' 9的个数不够注意事项：格式中不能和小数表达写在一起，但是可以混合货币等。19 X xxxx转换为 16 进制。TO_CHAR(100,'XX')= 64注意事项：数值必须是大于等于0 的整数。前面只能和0 或者 FM 组合使用 .20 通过以上的例子， 我们了解了各种数字的格式。 可以说格式太多样， 难于记在脑子，最好是作为一个参考存在着 .归类：数值类：0,9,分组类：(.),(,

27、),D,G , 其中点好和逗号因为表示不明显，所以用小括号凸显。货币类：$,C,L,U计算转换类：EEEE,RN,V,X正负符号： MI,PR,S其它类： B正统类： TM1.2 格式说明从上图可以看到格式是可选取的，保留字 fmt 也不是必须的， 关键是 NLSPARAM的意思，从第 2-54 章节（ FORMAT MODELS)查看。NLSPARAM 可以是这样 'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = ter

28、ritory '举例： (待续 )SELECT TO_CHAR(-10000, 'C99G999D99PR','NLS_NUMERIC_CHARACTERS=''._'' NLS_ISO_CURRENCY=''UNITED KINGDOM''') "Amount"FROM DUAL;结果返回： <GBP10_000.00>如果要了解可以使用的NLS_ISO_CURRENCY值 ,可以查询数据库的视图V_$NLS_VALID_VALUES二， TO_CHAR(C

29、HARACTER)把 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"略 时间分隔符

30、号，除了标准的几个，还允许用文字作为分割符号。例如 to_char(sysdate,'YYYY"年 "mm" 月"dd" 日 "')=2008 年 04 月 24 日2 ADA.D.即拉丁文Anno Domini的简写，表示公元 .会根据 nls 的不同转换为公元或者ad等无特殊注意事项3 AMA.M.上午的简写,同 pm, p.m.（下午 ) , 中文环境输出为上午（如果是上午）4 BCB.C.虽然标准的写法是B.c. (c 小写）或者 BC, 好在 Oracle 不讲究这个。表示公元前5 CCSCC返回世纪，以阿拉伯数字表示如果年的后两位介于01-99 那么，返回前两位+1 ，否则返回前两位6 D一周之中的某天，返回的是序号1-77 DAY一周之中的某天，不过返回的是星期几而已，这和语言设置有关系，在中国环境NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE，用星期一到星期天表示8 DD月份中的某天(1-31)9 DDD年份中的某天（1-366 ）10 DL 'DL'返回长的日期格式。受到 NLS_TERRITORY,NLS_LANGUAGE参数控制。例 2008 年4月28日星期一限制：除了DL ，其它什么的都不能设置。11 DS返回短的日期格式。