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.
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.
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 |