代码改变世界

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

2015-12-28 23:45  潇湘隐者  阅读(12600)  评论(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