Reputation: 615

Oracle number data type issue

I'm trying to save a number of certain characters(let's say 4) in my oracle 11g db. My numbers has a leading zero such 0100 0101 etc when i say the data. Why does oracle truncate the leading zero. Is it possible to avoid this. I really want the leading zero to get saved. Please help.

Thanks!

Upvotes: 0

Answers (4)

David Aldridge

Reputation: 52396

If you want to display the number with a leading zero then use:

to_char(my_number,'fm0000')

If you really need to store the number with a leading zero then store it as a varchar2, because what you have there is a string that consists only of digits, not a number.

Upvotes: 5

Ed Gibbs

Reputation: 26363

The TO_CHAR answers above are best, but they need a tweak because TO_CHAR(number, '0000') will add leading zeros plus a leading space, which is a placeholder in case there's a negative sign:

SELECT '[' || TO_CHAR(123, '0000') || ']' "Format" FROM DUAL;

Format
-------
[ 0123] <-- note the leading space

To get rid of the space, either LTRIM or use the FM qualifier in the format string:

SELECT '[' || TO_CHAR(123, 'FM0000') || ']' "Format" FROM DUAL;

Format
-------
[0123] <-- no more leading space

Upvotes: 3

Egor Skriptunoff

Reputation: 23767

Simple trick to get 8 digits:

substr(your_number + power(10,8), 2)

Upvotes: -2

Gordon Linoff

Reputation: 1271161

If you want to store the leading zero, then store the number as a char() for fixed length values or varchar() (or varchar2()) for variable length values.

Numbers are stored in a binary format, which has no concept of leading zeros.

Upvotes: 1

Oracle number data type issue

Answers (4)

Related Questions