Home » SQL & PL/SQL » SQL & PL/SQL » leading 0's trimmed from number column
leading 0's trimmed from number column [message #36235] Mon, 12 November 2001 11:38 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
Hi,
I have a column with datatype number(10) that has numbers such as 0012345678. Oracle's number property trims these leading zero's however. How can I keep those 0's? Thanks.
Tony

----------------------------------------------------------------------
Re: leading 0's trimmed from number column [message #36238 is a reply to message #36235] Mon, 12 November 2001 12:58 Go to previous messageGo to next message
oraboy
Messages: 97
Registered: October 2001
Member
if it is in SQL..
use format

SQL> column {columnname} format 0000000099.99

----------------------------------------------------------------------
Re: leading 0's trimmed from number column [message #36260 is a reply to message #36235] Tue, 13 November 2001 10:03 Go to previous messageGo to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
I believe that's how I change the output for when I select the numbers from the column. However, I insert a number like 0001231234, and it is stored as 1231234 in the database...how can I store those leading zero's? Also, if I have a number such as 1234567, how can I pad 3 zero's to the left of that number?

----------------------------------------------------------------------
Re: leading 0's trimmed from number column [message #36271 is a reply to message #36235] Wed, 14 November 2001 04:04 Go to previous message
oraboy
Messages: 97
Registered: October 2001
Member
nope! when you store 00001234 into a numeric field it goes only as 1234.
if you want to store as such you have to choose VARCHAR datatype

Otherwise, you can try setting NLS_FORMAT so that all numbers would have a format of 0000099.99
( like NLS_DATE_FORMAT)
Am not sure whether NLS_NUMBER_FORMAT is available

Regards
oraboy

----------------------------------------------------------------------
Previous Topic: Multiple Inline Queries - Urgent
Next Topic: Re: Help Convert VARCHAR2 to number
Goto Forum:
  


Current Time: Tue Jun 02 10:37:27 CDT 2020