ITPub博客

首页 > 数据库 > Oracle > 如何将一个数字转换为字符串并且按照指定格式显示?--TO_CHAR

如何将一个数字转换为字符串并且按照指定格式显示?--TO_CHAR

原创 Oracle 作者:lhrbest 时间:2017-12-26 11:30:58 0 删除 编辑

如何将一个数字转换为字符串并且按照指定格式显示?--TO_CHAR




1.1.1.1      如何将一个数字转换为字符串并且按照指定格式显示?

在应用中,可能需要将0.007007040000转换成0.70%,或需要显示“0.00”、“1.20”等类似的数据格式,此时可以使用TO_CHAR函数来转换。这个函数可以用来将DATENUMBER数据类型转换成可显示的字符串,格式是TO_CHAR(number_type,format_mask)format_mask转换格式有多种,如下表所示:

格式

含义

9

显示数字,9表示对应的某一个指定位数的值,若值是0则忽略不显示,若指定位数没有值,则以空格表示。

0

显示数字,0表示对应的某一个指定位数的值,若值是0则显示为0,若指定位数没有值也显示为0

FMfm

FM表示将显示出来的字符串定位数没有值而显示的空格清理掉,作用和ltrim类似。

.

在指定位置显示小数点。

,

在指定位置显示逗号。

$

在数字前加美元。

L

在数字前面加本地货币符号。

C

在数字前面加国际货币符号。

G

在指定位置显示组分隔符。

D

在指定位置显示小数点符号(.)。

PR

尖括号内负值。

MI

在指明的位置的负号(如果数字 < 0)。

PL

在指明的位置的正号(如果数字 > 0)。

S

带负号的负值(使用本地化)。

SG

在指明的位置的正/负号。

RN

罗马数字(输入在 1 3999 之间)。

THth

转换成序数。

V

移动 n 位(小数)

EEEE

科学记数。现在不支持。

需要注意的是,在NUMBER类型转换为字符串时,负数会返回前面包含负号的字符串,正数则会返回前面包含空格的字符串,除非格式模式包含MIS、或PR元素。即LENGTH(TO_CHAR(4, '0000'))的值其实是5,所以需要使用FM格式或ltrim去掉空格。示例如下所示:

SYS@PROD1> SELECT TO_CHAR(0.00, 'FM9999999999999999990.00') A,

  2         TO_CHAR(ROUND(0.007007040000, 4) * 100, 'FM99999999990.90') || '%' AS B,

  3         LENGTH(TO_CHAR(4, '0000')) C,

  4         LENGTH(TO_CHAR(4, 'FM0000')) C1,

  5         LENGTH(LTRIM(TO_CHAR(4, '0000'))) C2

  6    FROM DUAL;

 

A                       B                         C         C1         C2

----------------------- ---------------- ---------- ---------- ----------

0.00                    0.70%                     5          4          4

 

真题1、  如何将数字格式化显示并且依然返回数值类型?

答案:可以使用CAST函数,示例如下所示:

SYS@PROD1> SELECT CAST(1234.4 AS NUMBER(10, 2)) AS A,

  2         CAST(12 AS NUMBER(10, 2)) AS B,

  3         CAST(0 AS NUMBER(10, 2)) AS C,

  4         CAST('1234.4' AS NUMBER(10, 2)) AS A1,

  5         CAST('12' AS NUMBER(10, 2)) AS B1,

  6         CAST('0' AS NUMBER(10, 2)) AS C1

  7    FROM DUAL;

 

           A            B            C           A1           B1           C1

------------ ------------ ------------ ------------ ------------ ------------

    1234.40        12.00         0.00      1234.40        12.00         0.00

 

 




oracle to_char函数将number转成string

很多数据转换处理操作时,会遇到将0.007007040000转换成0.70%的需求,我们使用Oracle 的SQL 函数 to_char可以实现这种转换。 
这个函数用来将DATE或NUMBER数据类型转换成可显示的字符串,格式是to_char(number_type, format_mask)。 
格式'999.99',9表示对应的某一个指定位数的值,如果是值是0则忽略不显示,如果指定位数没有值,则以空格表示。 
格式'0990.990',0表示对应的某一个指定位数的值,如果是值是0则显示为0,如果是没有值也显示为0。 
格式'FM990.90',FM表示将显示出来的字符串定位数没有值而显示的空格清理掉,作用和ltrim类似。

以下为代码示例:

[sql] view plain copy
  1. SQL> select to_char(12304.560,'999.99'from dual;   
  2. TO_CHAR(12304.560,'999.99')   
  3. ---------------------------   
  4. #######   
  5. SQL> select to_char(104.560,'999.99'from dual;   
  6. TO_CHAR(104.560,'999.99')   
  7. -------------------------   
  8. 104.56   
  9. SQL> select to_char(104.560,'99999.99'from dual;   
  10. TO_CHAR(104.560,'99999.99')   
  11. ---------------------------   
  12. 104.56   
  13. SQL> select to_char(104.560,'99999.990'from dual;   
  14. TO_CHAR(104.560,'99999.990')   
  15. ----------------------------   
  16. 104.560   
  17. SQL> select to_char(104.560,'0099.990'from dual;   
  18. TO_CHAR(104.560,'0099.990')   
  19. ---------------------------   
  20. 0104.560   
  21.   
  22. SQL>   
  23. SQL> select to_char(round(0.007007040000, 4) * 100, 'FM99999999990.90') || '%' as aa,   
  24. 2 length(to_char(round(0.007007040000, 4) * 100, 'FM99999999990.90') || '%'as bb   
  25. from dual;   
  26. AA BB   
  27. ---------------- ----------   
  28. 0.70% 5   
  29. SQL>   
  30. SQL> select to_char(round(0.007007040000, 4) * 100, '99999999990.90') || '%' as aa,   
  31. 2 length(to_char(round(0.007007040000, 4) * 100, '99999999990.90') || '%'as bb   
  32. from dual;   
  33. AA BB   
  34. ---------------- ----------   
  35. 0.70% 16   


这是to_char将数字转换成字符串的常见操作功能,还有其他一些格式,见to_char(numeric)的格式模板:

[html] view plain copy
  1. 模板 描述   
  2. 9 带有指定位数的值   
  3. 0 前导零的值   
  4. . (句点) 小数点   
  5. , (逗号) 分组(千)分隔符   
  6. PR 尖括号内负值   
  7. S 带负号的负值(使用本地化)   
  8. L 货币符号(使用本地化)   
  9. D 小数点(使用本地化)   
  10. G 分组分隔符(使用本地化)   
  11. MI 在指明的位置的负号(如果数字 < 0)   
  12. PL 在指明的位置的正号(如果数字 > 0)   
  13. SG 在指明的位置的正/负号   
  14. RN 罗马数字(输入在 1 和 3999 之间)   
  15. TH or th 转换成序数   
  16. V 移动 n 位(小数)  
  17. EEEE 科学记数。现在不支持。   


ORACLE TO_CHAR函数格式化数字的出现空格的原因

2015-12-28 23:45 by 潇湘隐者, 2736 阅读, 0 评论, 收藏编辑

   在这篇博客SQL挑战——如何高效生成编码里面我由于需要将数字格式化为字符,像12需要格式化0012这样的字符,所以使用了TO_CHAR(数字,'0000')这样的写法,后面0000表示缺省补零,测试过程中,我发发现TO_CHAR(number,'0000') 会多一个空格。有点纳闷为什么了会多一个空格。

clip_image001

后面经过查看官方文档、同事讨论以及网友提供的资料,终于把这个问题给弄清楚了。下面梳理、总结于此,希望对其它人有所帮助。

在官方文档https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570里面你能看到一段介绍

Number Format Elements

A number format model is composed of one or more number format elements. The tables that follow list the elements of a number format model and provide some examples.

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

注意红色部分,意思是Number类型转换为字符时,负数会返回前面包含负号的字符串,正数则会返回前面包含空格的字符串,除非格式模式包含MI、S、或PR元素。查看TO_CHAR(4, '0000')返回的字符串长度,你会发现其长度为5.

 1: SQL> SELECT TO_CHAR(4, '0000') FROM DUAL;
 2:  
 3: TO_CH
 4: -----
 5:  0004
 6:  
 7: SQL> SELECT LENGTH(TO_CHAR(4, '0000')) FROM DUAL;
 8:  
 9: LENGTH(TO_CHAR(4,'0000'))
 10: -------------------------
 11:                         5
 12:  
 13: SQL> SELECT TO_CHAR(-4, '0000') FROM DUAL;
 14:  
 15: TO_CH
 16: -----
 17: -0004

clip_image002

那么如何解决这个问题呢,目前有两种方法,一种方法是用TRIM去空格,另外一种是使用参数MI(MI参数是正数的空格放到字符串后面,将负数的负号放置在字符串后面),关于参数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.

      返回负值与尾随的负号 (-)。

      返回正值尾随空白。

      限制: MI 格式元素可以只能出现在数字的格式模式的最后一个位置。

clip_image003

 

其实这些还只是TO_CHAR函数的冰山一角,如果你通读文档https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570那么你会发现其实你所掌握的TO_CHAR函数还只是个皮毛而已。一个TO_CHAR函数如此强大,可见ORACLE的博大精深。

 

参考资料:

    http://blog.163.com/wangpeng126_2009@126/blog/static/121022611201454111640254/ 

                  https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570

 



oracle使用to_char把数字转换成字符串时有空格  

Oracle to_char():数字转字符串,结果添加空格: 
select   to_char(12,'00')   from   dual   
字符串是   '   12',前面多了一个空格, 
select   length(to_char(12,'00'))   from   dual    
返回结果是:3   
返回的字符串总是前面有个空格。原因: 
那个空格位置是放符号的,正的数字就空了,负的就是一个‘-’号而没有空格。 
这是网上流传的原因: 

FM 

Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function: 

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary. 

In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number. 

Track back 



解决办法: 
select   trim(to_char(12,'00'))   from   dual 
或者 
select   to_char(12,'fm00')   from   dual 

总结: 
select   to_char(12,'00000')   from   dual 结果为: 
00012(1个空格) 
select   to_char(12,'99999')   from   dual 结果为: 
12(4个空格) 
select   to_char(-12,'00000')   from   dual 结果为: 
-00012(无个空格) 
select   to_char(-12,'99999')   from   dual 结果为: 
-12(3个空格)
转自:http://hi.baidu.com/jeschou/item/94edc1f1126d4a0ac7dc4550


Oracle数据库,数字强制显示2位小数


今遇到一个需求,如题,要求将数字以两位小数的格式显示,如果没有小数,则强制显示为0。
例如:
123.4 显示为 123.40
12    显示为 12.00
0     显示为 0.00


本以为这是个比较简单的问题,Oracle本身提供了to_char函数,带有格式化功能,能够满足条件:

[sql] view plain copy
  1. select to_char(123.4, '9999990.00'as aa from dual;  
  2. select to_char(12, '9999990.00'as aa from dual;  
  3. select to_char(0, '9999990.00'as aa from dual;  
  4.   
  5. select to_char(123.4, 'fm9999990.00'as aa from dual;  
  6. select to_char(12, 'fm9999990.00'as aa from dual;  
  7. select to_char(0, 'fm9999990.00'as aa from dual;  

至此,本以为问题解决了,但是却没有注意到,以上的语句格式化的是字符串,而不是数字!!
需求中,很明确的要求,将数字格式化,结果仍然为数字。


分析:该需求是一个非常常见、且正常的需求,既然Oracle如此强大,应该会提供相关的方法,
于是乎,查找Oracle的相关文档,终于,找到个有个cast函数,该函数负责类型转换,尝试之。
结果显示,该方法确实可行。测试SQL语句如下:

[sql] view plain copy
  1. select CAST(1234.4 AS NUMBER (10, 2) ) as aa from dual ;  
  2. select CAST(12 AS NUMBER (10, 2) ) as aa from dual ;  
  3. select CAST(0 AS NUMBER (10, 2) ) as aa from dual ;  


PS:追加一点,字符串可以直接进行类型转换,而无需使用to_number()函数做中间转换。SQL语句如下:

[sql] view plain copy
  1. select CAST('1234.4' AS NUMBER (10, 2) ) as aa from dual ;  
  2. select CAST('12' AS NUMBER (10, 2) ) as aa from dual ;  
  3. select CAST('0' AS NUMBER (10, 2) ) as aa from dual ;  

PS:追加第二点,网上看到有人说,小数点后是否显示完全,PL/SQL的版本有关。
本人未做验证,不发表个人意见,仅在此记录一下,如以后遇到问题,再行验证。


关于处理小数点位数的几个oracle函数() 1. 取四舍五入的几位小数 select round(1.2345, 3) from dual;
结果:1.235 2. 保留两位小数,只舍 select trunc(1.2345, 2) from dual;
结果:1.23 select trunc(1.2399, 2) from dual;

结果:1.23 3.取整数
返回大于或等于x的最大整数:  
        SQL> select ceil(23.33) from dual;  
结果: 24 返回等于或小于x的最大整数:  
        SQL> select floor(23.33) from dual;  
结果: 23 返回舍入到小数点右边y位的x值:rcund(x,[y])  
        SQL> select round(23.33) from dual;  
结果: 23 返回截尾到y位小数的x值:trunc(x,[y])  
        SQL> select trunc(23.33) from dual;  
结果: 23 格式化数字

 The following are number examples for the to_char function.


  to_char(1210.73, '9999.9') would return '1210.7'   to_char(1210.73, '9,999.99') would return '1,210.73'   to_char(1210.73, '$9,999.00') would return '$1,210.73'   to_char(21, '000099') would return '000021' to_char函数特殊用法
  to_char(sysdate,'d') 每周第几天
  to_char(sysdate,'dd') 每月第几天
  to_char(sysdate,'ddd') 每年第几天
  to_char(sysdate,'ww') 每年第几周
  to_char(sysdate,'mm') 每年第几月
  to_char(sysdate,'q') 每年第几季
  to_char(sysdate,'yyyy') 年
  比如要找某个时间为每周第几天就可以
  SQL> select to_char(to_date('20070101','yyyymmdd'),'d') from dual; 1.instr

 

在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。

 

语法如下:instr( string1, string2 [, start_position [, nth_appearance ] ] )

 

string1 源字符串,要在此字符串中查找。

string2 要在string1中查找的字符串.

start_position 代表string1 的哪个位置开始查找。此参数可选,如果省略默认为1. 字符串索引从1开始。如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。

 

nth_appearance 代表要查找第几次出现的string2. 此参数可选,如果省略,默认为 1.如果为负数系统会报错。


注意:

如果String2在String1中没有找到,instr函数返回0.

 

应用于:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
举例说明: select instr('abc','a') from dual; -- 返回 1  select instr('abc','bc') from dual; -- 返回 2 select instr('abc abc','a',1,2) from dual; -- 返回 5 select instr('abc','bc',-1,1) from dual; -- 返回 2 select instr('abc','d') from dual; -- 返回 0  注:也可利用此函数来检查String1中是否包含String2,如果返回0表示不包含,否则表示包含。


    一 ,TO_CHAR(NUMBER)
      1.1  格式图和简单说明
       
      本函数把参数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') 
注意事项:只能出现一次.
 4  0(零) '0999.99' 
 零.在对应位置返回对应的字符,如果没有则以'0'填充.
to_char(0.34,'9,9,9,0.$99')='$0.34';to_char(1234,'9999.00')='1234.00';
注意事项:这是一个强制的符号,对应位没有,则以'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'  没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置.
'S'||TO_CHAR(1234,'99B99')='S 1234';
注意事项:只能出现在整数部位.
 7  C(国际货币符号)  'C9999' 在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值)
 TO_CHAR(1233,'C9999')='CNY1234' ,这是新的国际标准RMB,关于这个可查询"国际货币符号"
注意事项:只能出现在整数部位第一位.
可以通过alter session set NLS_ISO_CURRENCY='JAPAN';来修改当前会话的设置.
 8  D(ISO 小数位符号) '999D99'
 这是"点号"的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.
注意事项:没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.
也可用alter sesssion set 来修改.
alter session set nls_numeric_characters='!,';   to_char(1234.34,'9999d99')=1234!34
 9  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, 此外如果
要转换出小数点,则要和D配合使用,不能和点号配合.
 11  L(本地货币符号)  'L999' 是C的本地版本.可以放在整个格式的最前面和最后面.
TO_CHAR(123456,'999G9G99D00L')=123,4,56.00¥
注意事项:同第七项 C
 12  MI(负号) '9999MI' 
如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格
to_char(1234,'9999mi')||'S'||TO_CHAR(-5678,'9999MI') =1234 S5678-
注意事项:只能放在格式尾巴
 13  PR(符号)  9999PR  是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来.
TO_CHAR(-1234.89,'9G999D00PR')=<1,234.89>
注意事项:同12
 14  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;
注意事项:只能自己使用,不能和其它符号组合使用.
 15  S  '9999S'  是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后.
TO_CHAR(-1234,'S9999')=-1234;TO_CHAR(1234,'S9999')=+1234
 16  TM  TM9/TMe
 使用这个参数等于没有用参数to_char(number)一样,应为'tm9'是默认的格式参数.
to_char(1234,'tme')=1234
注意事项:格式要么是TM9,要么是TME.
当数字长度超过64位时候,TM9的输出等同于TME的输出.
 17  U  U999  双币符号,例如欧元.作用同11的L
TO_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;
TO_CHAR(5,'9V9')=5*10=50
TO_CHAR(5,'9V99')=500
TO_CHAR(50,'9V99')='######'  9的个数不够
注意事项:格式中不能和小数表达写在一起,但是可以混合货币等。
 19  X  xxxx 转换为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章节(FORMAT MODELS)查看。
      NLSPARAM可以是这样 'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
  举例:(待续)
  SELECT TO_CHAR(-10000, 'C99G999D99PR',
               'NLS_NUMERIC_CHARACTERS=''._'' NLS_ISO_CURRENCY=''UNITED KINGDOM''') "Amount"
       FROM DUAL;
       结果返回: 
       如果要了解可以使用的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日
 2  AD
A.D.
   即拉丁文Anno Domini的简写,表示公元.会根据nls的不同转换为公元或者ad等
无特殊注意事项
 3  AM
A.M.
   上午的简写 ,同pm, p.m. (下午) , 中文环境输出为上午(如果是上午)
 4  BC
B.C.
   虽然标准的写法是B.c. (c小写) 或者BC,好在Oracle不讲究这个。表示公元前
 5  CC
SCC
   返回世纪,以阿拉伯数字表示
如果年的后两位介于01-99那么,返回前两位+1,否则返回前两位
 6  D    一周之中的某天,返回的是序号1-7
 7  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    返回短的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。 例如 2008-04-28
限制:除了DL,其它什么的都不能设置。
 12  DY    日期的简称,就是星期几(当然这指的是中国环境下)
 13  E    纪元简称,但是只适合以下集中日历:日本皇室,中华民国,太过佛历
 14  EE    纪元全程,适合情况同E
 15  FF [1..9]    就是毫秒,如果不更上数字就是用默认的精度。
只能用于timestamp类型的。
 16  FM    值得注意的一个函数:不返回任何内容。
有点不明白oracle为什么设置这个东西.
 17  FX    同上
 18  HH    表示小时,为12小时制,同hh12(1-12)
 19  HH12    表示小时,为12小时制(1-12)
 20  HH24    表示小时,为24小时制(0-23)
 21  IW    ISO标准的星期序号(1-52,或者1-53)
 22 IYYY 
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
 23  J    儒略日(多用于天文的一种日历),从公元前4712年一月一日算起,得出的结果是个整数,算法大体为 (公元日期+4712)*儒略日历年平均天数
 24  MI    秒(0-59)
 25  MM    2位月(1-12)
 26  MON    月的简称,和国家有关系NLS_DATE_LANGUAGE,例如04在中文环境下用4月表示.
 27  MONTH    月的名称,国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。
 28  PM
P.M.
   同am,a.m.表示下午
 29  Q    季度(1-4)
 30  RM    用罗马数字表示的月份,I   ,II  ,III ,IV  ,V   ,VI  ,VII ,VIII,IX  ,X   ,XI  ,XII 
 31  RR    有点四舍五入表示年的意思,具体的用法有那么一点点复杂。
以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
简而言之就是靠近当前年份原则,如果和当前年份同区域那么就一样,如果比当前区域大,那么就是当作是当前世纪前一世纪,否则就是下一个世纪。
举例来说,以to_date为例子
SQL> select to_date('89-01-01','rr-mm-dd') ,to_date('12-01-01','rr-mm-dd') FROM DUAL;

TO_DATE('89-01-01','RR-MM-DD') TO_DATE('12-01-01','RR-MM-DD')
------------------------------ ------------------------------
1989-01-01                     2012-01-01
我想oracle会搞这个东东出来,估计有两个考虑一个是为了方便,一个是为了对付百年或者千年问题。
 32  RRRR    如果输入参数只有两位,则同rr,否则就同yyyy作用.
 33  SS    秒(0-59),一分钟内
 34  SSSSS    一天从午夜开始的累积秒数.(0-86399)
 35  TS    返回短日期格式内容,包括时分秒等,只能和dl,ds组合使用,格式是:
dl ts或者dl ts ,中间以空格间隔开。TO_CHAR(SYSDATE,'TS')=下午 4:50:04
表现形式受NLS_TERRITORY 和NLS_LANGUAGE影响。
 36  TZD    夏令时制信息,时区简写加上夏令时信息,必须和格式tzr设置的时区对应。
包括下面三个TZ开头的,都是和时区相关,并不是直接用在to_char
 37  TZH    时区中的小时,例如hh:mi:ss.fftzh:tzm'
 38  TZM    时区中的分钟.
 39  TZR    时区中的区域信息,必须是数据库支持的时区,例如US/Pacific
 40  WW    和iw类似,也是表示星期的序号,从年的第一天算起到年的最后一个第七天。二者取值基本相同。(1-53) ,例如2008-01-01  到2008-01-07 算1,2008-01-09~2008-01-13 算2 
 41  W    一个月中的星期序号,其算法同ww,不过是局限在一月之内而已,和iso的不同。
 42  X    代表本地根符号,没有特别用处,只能和timestamp类型一起使用.
 43  Y,YYY    四位年,用都好分隔 例如2,008
 44  YEAR
SYEAR
   发音表达的年,例如 2008=two thousand eight
S前缀表示公元前BC
 45  YYYY
SYYYY
   四位年,S前缀表示公元前BC
 46  YYY
YY
Y
   一次表示后面3,2,1位的年,例如2008 可以分别取值为008,08,8
   总结    从以上看,主要就是表示时间几个部分的格式:世纪、年,月,日,时,分,秒,毫秒,以及其它一些混合格式。每个时间部分都可以有多种的表达方式,通过这样归类就比较容易记忆。
  很多格式可以组合使用,这样最终可以形成足够丰富的表达其形势;
 其次很多格式和nls是密切相关的;最后某些输出(返回)和格式大小写是有关系的,这在中文环境下体现不出来(目前来没有看到),但是english环境下就名下,以to_char(sysdate,'day')为例子,如果是西文环境是返回sun(假设sysdate位于周末),如果to_char(sysdate,'DAY')则返回SUN



使用前对一些不确定的先试验一下最好,实在不行就查阅oracle的原始文档.




       由于可以采取非常多的格式,所以实在是很灵活的.下面简单的举例几个:
       SQL> select to_char(sysdate,' PM yyyy-mm-dd hh24:mi:sssss AD   year mon day ddd iw') FROM DUAL;
       TO_CHAR(SYSDATE,'PMYYYY-MM-DDH
       --------------------------------------------------------------------------------
      上午 2008-03-27 09:58:35917 公元   two thousand eight 3月  星期四 087 13
       SQL> SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF5') FROM DUAL;
       TO_CHAR(SYSTIMESTAMP,'HH24:MI:
       ------------------------------
       10:02:28.90000
       SQL>SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL
       TO_CHAR(SYSDATE,'DSDL')
        -----------------------------------
       2008-03-27 2008年3月27日 星期四
       最后一个和国家地区有关.









About Me

.............................................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面宝典》读者群       小麦苗的微店

.............................................................................................................................................


DBA笔试面试讲解群
《DBA宝典》读者群 欢迎与我联系



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2149299/,如需转载,请注明出处,否则将追究法律责任。

下一篇: MySQL临时表
请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1153
  • 访问量
    7089840