8 Replies Latest reply on May 30, 2011 12:51 PM by Frank Kulash

    Number format (trailing zeros)

    JackK
      Hi,
      I'm confused with number formats. Please someone to explain me that.
      I thought that:
      SELECT to_char(1230, '9,999.99') AS frm FROM dual;
      would result in
      FRM
      -----
      1,230
      but the result is
      FRM
      --------
      1,230.00
      Why are the trailing zeros there? The format is not '9,999.00'.
        • 1. Re: Number format (trailing zeros)
          Solomon Yakobson
          You must use FM modifier:
          SQL> SELECT to_char(1230, 'FM9,999.99') AS frm FROM dual;
          
          FRM
          ---------
          1,230.
          
          SQL> 
          SY.

          Edited by: Solomon Yakobson on May 29, 2011 6:48 AM
          • 2. Re: Number format (trailing zeros)
            JackK
            Hmm... The documentation says (SQL Language Reference: Table 2-13 Number Format Elements):
            Element   Example  Description
                  0      0999  Returns leading zeros.
                         9990  Returns trailing zeros.
            What format must I then use to get the result
            FRM
            -------
            001,230
            for the number 1230 and
            FRM
            ---------
            001,230.9
            for the number 1230.9 (one format)?
            • 3. Re: Number format (trailing zeros)
              Frank Kulash
              Hi,

              I don;t think you can get those results sith TO_CHAR alone.
              Using FM, as Solomon suggested, may leave you with a decimal point at the end. You can remove that using RTRIM, like this:
              SELECT     RTRIM ( TO_CHAR ( 1230.9
                             , 'FM000,000.00'
                             )
                         , '.'
                         )          AS x
              FROM     dual;
              1 person found this helpful
              • 4. Re: Number format (trailing zeros)
                MichaelS
                What format must I then use to get the result
                SQL> with t as(
                 select 1230 n from dual union all
                 select 1230.9 n from dual
                )
                --
                --
                select to_char (n, case when trunc (n) = n then '000G999' else '000G999D09' end) n from t
                /
                N              
                ---------------
                 001,230       
                 001,230.90    
                
                2 rows selected.
                1 person found this helpful
                • 5. Re: Number format (trailing zeros)
                  JackK
                  Why '9,999.99' format for the number 1230 returns '.00' at the end even when there is '.99' in the format not '.00'. The zeros are trailing zeros so they should not be visible if the format ends with '.99' (according to the documentation).

                  So when may the '9990' format, which returns trailing zeros, be usefull?
                  • 6. Re: Number format (trailing zeros)
                    JackK
                    Explain me that, please... Is the documentation wrong?
                    • 7. Re: Number format (trailing zeros)
                      860993
                      more one way.
                      with t as(
                       select 1230 n from dual union all
                       select 1230.9 n from dual)
                      select regexp_replace(TO_CHAR(n),'\.0*$') from t
                      • 8. Re: Number format (trailing zeros)
                        Frank Kulash
                        Hi,
                        JackK wrote:
                        Explain me that, please... Is the documentation wrong?
                        It's not wrong. I'd say it was misleading.
                        '9990' returns trailing 0's, as the documentation says.
                        '9999' returns trailing 0's, too. Trailling 0's are always included, up to the number of digits specified.
                        If you don't specify any number of digits (as in TO_CHAR (x, 'TM') or TO_CHAR (x)), then trailing 0's after the decimal point, and the decimal point itself, if not needed, are not displayed, which is exactly what you want. However, you also want a non-default format for the digits before the decimal point, so you've got to specify digits. I don't believe there's any way to combine
                        (1) explicit formatting before the decimal point, and
                        (2) default, TM-style formatting after that
                        in a single TO_CHAR call.