转换函数：TO_CHAR()用法之一 将一个数值转化成字符串

 格式：TO_CHAR(number,format) 即 TO_CHAR((数值，格式）

 

用法： 1、不指定格式的 TO_CHAR函数将数值转换成简单字符串形式。

        例：  TO_CHAR(123)       结果       123

              TO_CHAR(-123)      结果      -123

       

        2、用元素9的格式

         例：  TO_CHAR(1234,'9999')       结果        1234

               TO_CHAR(567,'9999')        结果         567

               TO_CHAR(-567,'9999')       结果        -567

               TO_CHAR(1234567,'9999')    结果        ####

               TO_CHAR(45.789,'9999')     结果          46

 

               TO_CHAR(567,'9999.99')       结果       567.00

               TO_CHAR(-567,'9999.99')      结果      -567.00

               TO_CHAR(1234567,'9999.99')   结果      #######

               TO_CHAR(45.789,'9999.99')    结果        45.79

 

               TO_CHAR(567,'9,999,999')        结果             567

               TO_CHAR(-567,'9,999,999')       结果            -567

               TO_CHAR(1234567,'9,9999,999')   结果        1,234,567

               TO_CHAR(0.44,'9,999,999')       结果                0

 

        3、用元素$的格式

         例：  TO_CHAR(1234,'$9999')       结果        $1234

               TO_CHAR(-567,'$9999')       结果        -$567

 

        4、用元素B的格式  如果被转换数值整数部分是0就显示为空格

         例：  TO_CHAR(567,'B999.9')         结果             567.0

               TO_CHAR(-567,'B999.9')        结果            -567.0

               TO_CHAR(0.44,'B999.9')        结果                .4

 

        5、用元素MI的格式  负数则转换成末尾带减号的数

         例：  TO_CHAR(567,'999MI')         结果             567

               TO_CHAR(-567,'999MI')        结果             567－

 

         6、用元素S的格式  转换成带有正负号的数字字符

         例：  TO_CHAR(567,'S999')         结果            +567

               TO_CHAR(-567,'S999')        结果            -567

               TO_CHAR(567,'999S')         结果             567+

               TO_CHAR(-567,'999S')        结果             567-

 

         7、用元素PR的格式  用尖括号包围负数

         例：  TO_CHAR(567,'9999PR')         结果           567

               TO_CHAR(-567,'9999PR')        结果          <567>

 

         8、用元素D的格式  在指定位置插入小数点

          例：  TO_CHAR(567,'9999D99')          结果           567.00

                TO_CHAR(-567,'9999D99')         结果          -567.00

                TO_CHAR(567.866,'9999D99')      结果           567.87

 

         9、EEEE 科学计数法

         例：  TO_CHAR(567,'9.9EEEE')          结果             5.7E+02

               TO_CHAR(45,'9.9EEEE')           结果             4.5E+01

               TO_CHAR(0.0666,'9.9EEEE')       结果             6.7E-02    

    

TO_CHAR函数名不虚传， 转换功能很强

原文转自于: http://blog.sina.com.cn/s/blog_4cbadf5a0100f7u7.html