1 2 Previous Next 16 Replies Latest reply on May 20, 2019 11:11 AM by Etbin

    Storing decimal number with ending zeros

    Robert_D.

      Hello,

       

      in my table I want to store strings converted to numbers, e.g. 1, 1.0, 1.1.

       

        CREATE TABLE ."MY_TABLE"

         ( "COL1" NUMBER

         ) ;

       

      insent into my_table (col1) values to_number('1');

      insent into my_table (col1) values to_number('1.0');

      insent into my_table (col1) values to_number('1.1');

       

      After this in SQL Developer I see values

      1,

      1,

      1.1

       

       

      How to store in column numbers with ending zeros like 1.0?

        • 1. Re: Storing decimal number with ending zeros
          CarlosDLG

          You don't have to worry about the format in which the numbers will be stored.  Just make sure you use the correct data type, and let Oracle decide the format in which the data will be stored.

           

          Think about the format when you are displaying or presenting the data.

           

          You can display the 1 in that format by using TO_CHAR (change the format model to the appropriate size of your numbers):

           

          SELECT

              TO_CHAR(col1, '9.9')

          FROM

              my_table;

          • 2. Re: Storing decimal number with ending zeros
            John Thorton

            Robert_D. wrote:

             

            Hello,

             

            in my table I want to store strings converted to numbers, e.g. 1, 1.0, 1.1.

             

            CREATE TABLE ."MY_TABLE"

            ( "COL1" NUMBER

            ) ;

             

            insent into my_table (col1) values to_number('1');

            insent into my_table (col1) values to_number('1.0');

            insent into my_table (col1) values to_number('1.1');

             

            After this in SQL Developer I see values

            1,

            1,

            1.1

             

             

            How to store in column numbers with ending zeros like 1.0?

            whole numbers (integers) do not contain any fractional zeros.

            If you say that whole numbers contain fractional zeros then why stop at one zero? How many fractional zeros does any integer really have, two, five, twenty, a gazillion?

            • 3. Re: Storing decimal number with ending zeros
              Robert_D.

              I want to store strings in NUMBER data type column in exactly format as they are, e.g.

               

              01.1111

              1.0

              1

              12

              1.55

              12.1

              66.0000

              • 4. Re: Storing decimal number with ending zeros
                KayK

                Hi Robert,

                as said above, the way oracle stores numbers can't be changed. The numbers have 38 digit. You can define how many digits are valid for your column, that's all.

                If you need strings then you have to use strings, but then you can't calculate with them in a simple way.

                regards

                Kay

                • 5. Re: Storing decimal number with ending zeros
                  Frank Kulash

                  Hi,

                  Robert_D. wrote:

                   

                  I want to store strings in NUMBER data type column in exactly format as they are, e.g.

                   

                  01.1111

                  1.0

                  1

                  12

                  1.55

                  12.1

                  66.0000

                  Numbers don't have any format.  1 and 1.0 and 0001.000000 are all exactly the same number.  There is absolutely no difference between them.

                   

                  Store numbers in NUMBER columns.  If you really need to distinguish 1 from 1.0 for some reason, then use a separate column (e.g., for the number of digits after the decimal point).

                  • 6. Re: Storing decimal number with ending zeros
                    John Thorton

                    Robert_D. wrote:

                     

                    I want to store strings in NUMBER data type column in exactly format as they are, e.g.

                     

                    01.1111

                    1.0

                    1

                    12

                    1.55

                    12.1

                    66.0000

                    Then you need to employ Worst Practice & store NUMBER as STRING.

                    Integer values contain NO fractional zeros in the real world.

                    You are free to display INTEGERS with as many fractional zeros as you deem appropriate.

                    How does Oracle know & decide how many fractional zeros should exist for EVERY whole number?

                    This requirement is totally daft!

                    • 7. Re: Storing decimal number with ending zeros
                      BluShadow

                      Blimey, this old question keeps on showing it's ugly head every now and then.

                       

                      Understand the number datatype and then you'll understand that you don't store the "format" of the number...

                       

                      See the community document:

                      PL/SQL 101 : DataTypes - NUMBER

                       

                      Having a "format" is what you do when you want to display data.  "format" shouldn't even be a consideration when storing things like numbers or dates.

                      • 8. Re: Storing decimal number with ending zeros
                        EdStevens

                        Robert_D. wrote:

                         

                        I want to store strings in NUMBER data type column in exactly format as they are, e.g.

                         

                        01.1111

                        1.0

                        1

                        12

                        1.55

                        12.1

                        66.0000

                        WHY is it a "requirement" that some integers (which by definition have no decimal component) be displayed with no decimal, while others are to be displayed with 1 or more trailing zeros?  What is the business case here?

                        • 9. Re: Storing decimal number with ending zeros
                          L. Fernigrini

                          I agree with all the people here explaining that 1.50 has actually the same (mathematical) value as 1.5.

                           

                          I do not know WHY you need to store numeric data with a particular format, since that is useless from the math point of view...

                           

                          But the only way to store the "text" exactly as it was entered is by storing it as text (varchar) that leads to other problems.

                           

                          If you actually NEED to store the numbers as text, then you may want to create a virtual column:

                           

                           

                          CREATE TABLE MyTable (COL1 VARCHAR2(100), COL1_N NUMBER AS (TO_NUMBER(COL1)) );

                          CREATE INDEX IX_MyTable_COL1_N ON MyTable(COL1_N);

                          --

                          INSERT INTO MyTable (col1) VALUES ('1');

                          INSERT INTO MyTable (col1) VALUES ('1.0');

                          INSERT INTO MyTable (col1) VALUES ('1.0000');

                          INSERT INTO MyTable (col1) VALUES ('1.1');

                          INSERT INTO MyTable (col1) VALUES ('1.10000');

                          INSERT INTO MyTable (col1) VALUES ('1,10000');

                          INSERT INTO MyTable (col1) VALUES ('1,A');

                          --

                          SELECT * FROM MyTable;

                          --

                          DROP TABLE MyTable;

                           

                          Result:

                           

                          Table created.

                          Index created.

                          1 row(s) inserted.

                          1 row(s) inserted.

                          1 row(s) inserted.

                          1 row(s) inserted.

                          1 row(s) inserted.

                          ORA-01722: invalid number ORA-06512: at "SYS.DBMS_SQL", line 1721 

                          ORA-01722: invalid number ORA-06512: at "SYS.DBMS_SQL", line 1721 

                          COL1COL1_N
                          11
                          1.01
                          1.00001
                          1.11.1
                          1.100001.1

                          5 rows selected.

                          Table dropped.

                           

                           

                          And use the virtual column for the MATHs, while showing the original column. That way you can keep the source format while still performing math operations in an efficient way.

                          • 10. Re: Storing decimal number with ending zeros
                            L. Fernigrini

                            The index is not required, but I created it to force the calculation of TO_NUMBER when inserting or updating data, thus, avoiding entering invalid "numbers" as text. If you do not create the index, then the last 2 INSERT will not fail, but the select will:

                             

                            Table created.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            1 row(s) inserted.

                            ORA-01722: invalid number

                            Table dropped.

                            • 11. Re: Storing decimal number with ending zeros
                              Paulzip

                              Robert_D. wrote:

                               

                              I want to store strings in NUMBER data type column in exactly format as they are, e.g.

                               

                              01.1111

                              1.0

                              1

                              12

                              1.55

                              12.1

                              66.0000

                              Then you don't understand the fundamentals of numbers.

                               

                              You're worrying about formatting - something you do to the number when you send data to the client.

                              • 12. Re: Storing decimal number with ending zeros
                                jaramill

                                Robert_D. wrote:

                                 

                                I want to store strings in NUMBER data type column in exactly format as they are, e.g.

                                 

                                01.1111

                                1.0

                                1

                                12

                                1.55

                                12.1

                                66.0000

                                Not to pile on to what everyone else is saying....but your problem is a "display" issue.  That's what the built-in function TO_CHAR is for.  You can "convert to a character" (based on datatype like DATE, NUMBER) into the legal forms that Oracle allows.

                                 

                                Read the documentation as others have pointed out...and here's the link on TO_CHAR --> https://docs.oracle.com/database/121/SQLRF/functions217.htm#SQLRF06130

                                Read the documentation the legal format models --> https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#i34510

                                • 13. Re: Storing decimal number with ending zeros
                                  Etbin

                                  Might be something connected to the concept of significant digits.

                                  Thinking more than forty years back at the university for the physicists and numerical analysts 1 and 1.0 seemed to be two very different things (not numbers).

                                   

                                  Regards

                                   

                                  Etbin

                                  • 14. Re: Storing decimal number with ending zeros
                                    Gaz in Oz

                                    TO_CHAR(number)

                                    For example:

                                    SQL> ed

                                    Wrote file afiedt.buf

                                     

                                      1  with x as (

                                      2     select 1 one from dual

                                      3  )

                                      4  select one,

                                      5         to_char(one, '9.0')       one,

                                      6         to_char(one, '0999.9990') one,

                                      7         to_char(one, '9.9990')    one,

                                      8         to_char(one, '99.99EEEE') one,

                                      9         'etc ...' " "

                                    10* from   x

                                    SQL> /

                                     

                                           ONE ONE  ONE        ONE     ONE

                                    ---------- ---- ---------- ------- ---------- -------

                                             1  1.0  0001.0000  1.0000   1.00E+00 etc ...

                                     

                                    1 row selected.

                                     

                                    SQL>

                                    1 2 Previous Next