你的位置:首页 > 数据库

[数据库]ORACLE TO_CHAR函数格式化数字的出现空格的原因


   在这篇博客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