I try to insert .2 to Oracle but it shows 0.2 in db

Viewed 24

I am trying to add value .2 to db, but it shows 0.2 after inserting. The datatype is number(3,2). What can I do, so it not add leading zero to the decimal numbers.

1 Answers

A NUMBER is a binary data-type that stores an exact representation of the value by storing the pairs of digits in each byte and it NEVER stores the number with a particular formatting.

The number .2 is exactly the same as the number 0.2 as they both have zero units and 2 tenths; the only difference is in how you want to format the value but the database does not store formatting with a NUMBER.

For example:

CREATE TABLE table_name (value NUMBER(3,2));

INSERT INTO table_name (value)
SELECT .2 FROM DUAL UNION ALL
SELECT 0.2 FROM DUAL UNION ALL
SELECT .20 FROM DUAL UNION ALL
SELECT 0.20 FROM DUAL;

Then:

SELECT value, DUMP(value) FROM table_name;

Outputs:

VALUE DUMP(VALUE)
.2 Typ=2 Len=2: 192,21
.2 Typ=2 Len=2: 192,21
.2 Typ=2 Len=2: 192,21
.2 Typ=2 Len=2: 192,21

and you can see that all the rows are identical and are stored with the same byte values.


If you want to format the number in a particular way then use TO_CHAR:

SELECT TO_CHAR(.2, '9.99'),
       TO_CHAR(.2, 'FM9.99'),
       TO_CHAR(.2, 'FM9.00'),
       TO_CHAR(.2, 'FM0.00')
FROM   DUAL
TO_CHAR(.2,'9.99') TO_CHAR(.2,'FM9.99') TO_CHAR(.2,'FM9.00') TO_CHAR(.2,'FM0.00')
.20 .2 .20 0.20

fiddle

Related