13 Replies Latest reply on Feb 28, 2013 10:01 AM by Paul M.

    number not showing zero after decmial.

    947771
      hi,
      i have a column
      NUMBER(13,4)

      when i insert 234.000

      in show only 234

      where as when i insert 234.12
      i shows 234.12

      is there any setting by which i can show 234.0000

      yours sincerly
        • 1. Re: number not showing zero after decmial.
          Frank Kulash
          Hi,
          944768 wrote:
          hi,
          i have a column
          NUMBER(13,4)

          when i insert 234.000

          in show only 234

          where as when i insert 234.12
          i shows 234.12

          is there any setting by which i can show 234.0000

          yours sincerly
          In SQL, you can use the TO_CHAR function to display numbers in a given format.
          TO_CHAR (x, '999999990.0000')
          will display the number x with at least 1 digit before the decimal point, and exactly 4 digits after the decimal point.

          Your front end may have better ways to handle formatting.
          In SQL*Plus, for example, you can use the COLUMN command to set the formatting for a given column:
          COLUMN  x   FORMAT  999999990.0000
          and SET NUMFORMAT to change the default presentation for all NUMBER columns:
          SET     NUMFORMAT  999999990.0000
          • 2. Re: number not showing zero after decmial.
            Paul M.
            SQL> select to_char(234.000,'999,999.0000') from dual;
            
            TO_CHAR(234.0
            -------------
                 234.0000
            
            SQL>
            • 3. Re: number not showing zero after decmial.
              Paul  Horth
              Yes, use a format mask in a to_char function. Maybe something like

              to_char(<your_column>, '999999999.9999')

              to get it to display with trailing zeroes.
              • 4. Re: number not showing zero after decmial.
                odie_63
                In addition to the previous replies, use FM modifier if you don't want the resulting string to be padded with whitespaces for missing digits and sign indicator :
                SQL> select to_char(1234, '999999990D9990') from dual;
                 
                TO_CHAR(1234,'999999990D9990')
                ------------------------------
                      1234,0000
                 
                SQL> select to_char(1234, 'fm999999990D9990') from dual;
                 
                TO_CHAR(1234,'FM999999990D9990
                ------------------------------
                1234,0000
                 
                • 5. Re: number not showing zero after decmial.
                  947771
                  column format is not working ?

                  what could be the reason.

                  number format is working but it changes all numbers which we do not want.

                  yours sincerlly

                  Edited by: 944768 on Feb 24, 2013 7:17 AM
                  • 6. Re: number not showing zero after decmial.
                    SomeoneElse
                    column format is not working ?
                    If only we could look over your shoulder.
                    • 7. Re: number not showing zero after decmial.
                      sb92075
                      944768 wrote:
                      column format is not working ?
                      my car is not working
                      tell me how to make my car go.



                      How do I ask a question on the forums?
                      SQL and PL/SQL FAQ
                      • 8. Re: number not showing zero after decmial.
                        Solomon Yakobson
                        944768 wrote:
                        number format is working but it changes all numbers which we do not want.
                        If you want to preserve exact input you shoudn't be using NUMBER datatype. NUMBER datatype stores numbers, not how they were typed in. No matter how you type number 12:

                        12
                        1.2e1
                        12.0

                        it is still number 12 and is stored as one. To preserve input (although I have no idea why one would need that), use VARCHAR2, not number and CHECK constraint which makes sure VARCHAR2 column holds numeric strings only:
                        SQL> create table tbl(
                          2                   numeric_string varchar2(10)
                          3                  )
                          4  /
                        
                        Table created.
                        
                        SQL> alter table tbl
                          2    add constraint tbl_chk1
                          3      check(
                          4            numeric_string - numeric_string = 0
                          5           )
                          6  /
                        
                        Table altered.
                        
                        SQL> insert
                          2    into tbl
                          3    values(
                          4           'abc'
                          5          )
                          6  /
                          into tbl
                            *
                        ERROR at line 2:
                        ORA-01722: invalid number
                        
                        
                        SQL> insert
                          2    into tbl
                          3    values(
                          4           '234.000'
                          5          )
                          6  /
                        
                        1 row created.
                        
                        SQL> insert
                          2    into tbl
                          3    values(
                          4           '234.12'
                          5          )
                          6  /
                        
                        1 row created.
                        
                        SQL> select  *
                          2    from  tbl
                          3  /
                        
                        NUMERIC_ST
                        ----------
                        234.000
                        234.12
                        
                        SQL>
                        SY.
                        • 9. Re: number not showing zero after decmial.
                          947771
                          to_char converts , but changes the type of col is possble to use any function to keep the type intact and get the formated result also.

                          yours sincerely.
                          • 10. Re: number not showing zero after decmial.
                            BluShadow
                            944768 wrote:
                            to_char converts , but changes the type of col is possble to use any function to keep the type intact and get the formated result also.
                            No.

                            A number is a number. As solomon already explained the number 12 is an identical number to 12.0 or 12.00000, and internally they are all stored as the same number because they are the same.

                            What you are asking for is a "display format" for displaying your numbers. Display formats relate to strings, so you have to convert your number to a string to display it in a different format. In SQL converting a number to a string of a required format is done using TO_CHAR. In certain interfaces like SQL*Plus etc. it provides commands that tell the SQL*Plus interface to display numbers with a specific format. But what you cannot do is actually store a number as a numeric datatype with a format.

                            If you look at what's stored on the database:
                            SQL> create table mynums (x number);
                            
                            Table created.
                            
                            SQL> insert into mynums (x) values (12);
                            
                            1 row created.
                            
                            SQL> insert into mynums (x) values (12.000);
                            
                            1 row created.
                            
                            SQL> insert into mynums (x) values (12.000000000);
                            
                            1 row created.
                            
                            SQL> select x, dump(x) as dmp from mynums;
                            
                                     X DMP
                            ---------- ----------------------------------------
                                    12 Typ=2 Len=2: 193,13
                                    12 Typ=2 Len=2: 193,13
                                    12 Typ=2 Len=2: 193,13
                            It doesn't matter how you type the number when you supply it to the insert statement, the number is stored internally the same, using an internal format (in the case of number 12 it stores the bytes of decimal value 193 and 13).

                            The internal format is not a straight representation of the number you supply...
                            SQL> select x, dump(x) as dmp from mynums;
                            
                                     X DMP
                            ---------- ----------------------------------------
                                    12 Typ=2 Len=2: 193,13
                                    12 Typ=2 Len=2: 193,13
                                    12 Typ=2 Len=2: 193,13
                                   100 Typ=2 Len=2: 194,2
                                  1001 Typ=2 Len=3: 194,11,2
                                 100.1 Typ=2 Len=4: 194,2,1,11
                                 10000 Typ=2 Len=2: 195,2
                            So just remember, what is stored internally, is not a formatted string... because formatted strings are what are used to 'display' things, not store them
                            • 11. Re: number not showing zero after decmial.
                              947771
                              is SET NUMFORMAT 999999990.0000





                              is session specific?

                              yours sincerly
                              • 12. Re: number not showing zero after decmial.
                                Paul  Horth
                                944768 wrote:
                                is SET NUMFORMAT 999999990.0000





                                is session specific?

                                yours sincerly
                                Yes.
                                • 13. Re: number not showing zero after decmial.
                                  Paul M.
                                  is SET NUMFORMAT 999999990.0000

                                  is session specific?
                                  Yes, unless you set it in some configuration file (e.g. login.sql or glogin.sql).