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

# Storing decimal number with ending zeros

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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