Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Question Unanswered: zero in front of a number

    Hi,

    I'm loading data from a flat file into Oracle9i using SQL*Loader. It works perfectly but I have one question.

    When I insert a number like '00001234' into a column with an INT datatype Oracle cuts of the zero's. When I select the rows after inserting the number Oracle shows me that the number '1234' is stored into the table. So my question is: is it posible to store a number into a table that has a zero in front of it?
    I think I have to use another datatype but I don't know which one? I have a book here about SQL that say's that there are some datatypes to store a number in (INTEGER, SMALLINT, TINYINT, FLOAT and DOUBLE) but it seams that none of them are aible to store a number with a zero in front of it.

    Joachim

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: zero in front of a number

    If you really need the leading zeroes (i.e. if this is something like a reference number rather than a numeric value), then you must store it in a VARCHAR2(9). There is no numeric datatype (INTEGER, SMALLINT, TINYINT, FLOAT and DOUBLE or whatever) that preserves the DISPLAY FORMAT of a number, they only store the VALUE. The VALUE of the number 000001234 is 1234.

    Of course, you can still DISPLAY the value with leading zeroes when you select it, e.g.

    SQL> select object_id from user_objects
    2 where rownum <= 5
    3 /

    OBJECT_ID
    ----------
    75004
    83530
    83525
    83133
    83134

    SQL> column object_id format 000000000

    SQL> select object_id from user_objects
    2 where rownum <= 5
    3 /

    OBJECT_ID
    ----------
    000075004
    000083530
    000083525
    000083133
    000083134

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27
    Hey thnx again,

    I think this will do fine.

    Joachim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •