1 2 Previous Next 15 Replies Latest reply: Feb 6, 2013 11:22 PM by Rahul_India RSS

    to char

    Rahul_India
      SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;


      y i am getting
      1234.5
        • 1. Re: to char
          Stew Ashton
          Oracle does implicit rounding, not implicit truncating. Try '99999' and it will round down.
          All number format models cause the number to be rounded to the specified number of significant digits.
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#SQLRF00211

          Edited by: Stew Ashton on Feb 3, 2013 9:04 PM
          • 2. Re: to char
            sb92075
            Rahul India wrote:
            SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;


            y i am getting
            1234.5
            what would you like instead & why that value?

            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: to char
              Ady Keeling
              Rahul India wrote:
              SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;


              y i am getting
              1234.5
              Because, you've only specified one decimal place. Try

              SELECT TO_CHAR(1234.49, '999999.90') FROM DUAL;

              This will give

              1234.49
              • 4. Re: to char
                stefan nebesnak
                It's not..
                "1234.5"
                But..
                "   1234.5"
                See..
                SELECT REPLACE(TO_CHAR(1234.49, '999999.999999'),' ','#') val FROM DUAL;
                
                .        VAL
                1     ###1234.490000
                SELECT REPLACE(TO_CHAR(1234.49),' ','#') val FROM DUAL;
                
                .        VAL
                1     1234,49
                • 5. Re: to char
                  John Spencer
                  Then you needto use the FM mosifier to trim extra spaces. Like:
                  SQL> SELECT REPLACE(TO_CHAR(1234.49, '999999.999999'),' ','#'),
                    2         REPLACE(TO_CHAR(1234.49, 'FM999999.999999'),' ','#')
                    3  FROM DUAL;
                  
                  REPLACE(TO_CHA REPLACE(TO_CHA
                  -------------- --------------
                  ###1234.490000 1234.49
                  John
                  • 6. Re: to char
                    rp0428
                    >
                    SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;

                    y i am getting
                    1234.5
                    >
                    So? You can't just post something and make a statement about it.

                    You need to ask a question or present an issue.

                    What did you expect to get? Why do you want to get what you expect to get?
                    • 7. Re: to char
                      Rahul_India
                      >
                      So? You can't just post something and make a statement about it.

                      You need to ask a question or present an issue.

                      What did you expect to get? Why do you want to get what you expect to get?
                      Ok i expected to get 1234.4
                      • 8. Re: to char
                        BluShadow
                        Rahul India wrote:
                        >
                        So? You can't just post something and make a statement about it.

                        You need to ask a question or present an issue.

                        What did you expect to get? Why do you want to get what you expect to get?
                        Ok i expected to get 1234.4
                        then, as you've been told, your expectation was wrong. Specifying a format mask doesn't just truncate the numeric value to the digits you specify. Oracle knows it's a number you're converting to a string, so when you tell it you only want 1 decimal place showing it automagically rounds the value for you, and .49 will round 'up' to .5
                        • 9. Re: to char
                          stefan nebesnak
                          Rahul India wrote:

                          Ok i expected to get 1234.4
                          You can use statement below as workaround.
                          select floor((1234.49)*10)/10 result from dual;
                          
                          RESULT
                          1234,4
                          • 10. Re: to char
                            Rahul_India
                            SELECT LENGTH(TO_CHAR(1234.49, '999999.9')) FROM DUAL;
                            Why i am getting output as 9?
                            I expected it to be 8.
                            • 11. Re: to char
                              stefan nebesnak
                              Rahul India wrote:
                              SELECT LENGTH(TO_CHAR(1234.49, '999999.9')) FROM DUAL;

                              Why i am getting output as 9?
                              I am expected it to be 8.
                              Oracle reserves the first character for (-) Sign.
                              SELECT
                              '999999.9' as format,
                              '1234.49' as value,
                              TO_CHAR(1234.49, '999999.9'),
                              REPLACE(TO_CHAR(1234.49, '999999.9'),' ','#'),
                              LENGTH(REPLACE(TO_CHAR(1234.49, '999999.9'),' ','#')) 
                              FROM DUAL
                              union all
                              SELECT 
                              '999999.9' as format,
                              '-1234.49' as value,
                              TO_CHAR(-1234.49, '999999.9'),
                              REPLACE(TO_CHAR(-1234.49, '999999.9'),' ','#'),
                              LENGTH(REPLACE(TO_CHAR(-1234.49, '999999.9'),' ','#')) 
                              FROM DUAL
                              union all
                              SELECT
                              '9999.9' as format,
                              '1234.49' as value, 
                              TO_CHAR(1234.49, '9999.9'),
                              REPLACE(TO_CHAR(1234.49, '9999.9'),' ','#'),
                              LENGTH(REPLACE(TO_CHAR(1234.49, '9999.9'),' ','#')) 
                              FROM DUAL
                              union all
                              SELECT 
                              '9999.9' as format,
                              '-1234.49' as value,
                              TO_CHAR(-1234.49, '9999.9'),
                              REPLACE(TO_CHAR(-1234.49, '9999.9'),' ','#'),
                              LENGTH(REPLACE(TO_CHAR(-1234.49, '9999.9'),' ','#')) 
                              FROM DUAL
                              union all
                              SELECT 
                              'FM999999.9' as format,
                              '1234.49' as value,
                              TO_CHAR(1234.49, 'FM999999.9'),
                              REPLACE(TO_CHAR(1234.49, 'FM999999.9'),' ','#'),
                              LENGTH(REPLACE(TO_CHAR(1234.49, 'FM999999.9'),' ','#')) 
                              FROM DUAL
                              union all
                              SELECT 
                              '999999.9 with trim()' as format,
                              '1234.49' as value,
                              TRIM(TO_CHAR(1234.49, '999999.9')),
                              REPLACE(TRIM(TO_CHAR(1234.49, '999999.9')),' ','#'),
                              LENGTH(REPLACE(TRIM(TO_CHAR(1234.49, '999999.9')),' ','#'))
                              FROM DUAL
                              Output:
                              _    FORMAT               VALUE       TO_CHAR(1234.49,'999999.9')  REPLACE(TO_CHAR(1234.49,'99999  LENGTH(REPLACE(TO_CHAR(1234.49
                              1    999999.9             1234.49     1234.5                       ###1234.5                       9
                              2    999999.9             -1234.49    -1234.5                      ##-1234.5                       9
                              3    9999.9               1234.49     1234.5                       #1234.5                         7
                              4    9999.9               -1234.49    -1234.5                      -1234.5                         7
                              5    FM999999.9           1234.49     1234.5                       1234.5                          6
                              6    999999.9 with trim() 1234.49     1234.5                       1234.5                          6
                              • 12. Re: to char
                                chris227
                                Rahul India wrote:
                                >
                                So? You can't just post something and make a statement about it.

                                You need to ask a question or present an issue.

                                What did you expect to get? Why do you want to get what you expect to get?
                                Ok i expected to get 1234.4
                                consider trunc
                                select
                                 trunc(1234.49, 1)
                                from dual
                                
                                TRUNC(1234.49,1)
                                1234,4
                                Edited by: chris227 on 05.02.2013 00:52
                                • 13. Re: to char
                                  Rahul_India
                                  SELECT replace(TO_CHAR('1234.49', '999999.9'),' ','#') FROM DUAL;


                                  Why i am getting an extra '#' and therefore length 9.i am expecting length to be 8 as there
                                  are 8 characters in FM.
                                  • 14. Re: to char
                                    Rahul_India
                                    SELECT replace(TO_CHAR('1234.49', '999999.9'),' ','#') FROM DUAL;


                                    Why i am getting an extra '#' and therefore length 9.i am expecting length to be 8 as there
                                    are 8 characters in FM.
                                    1 2 Previous Next