DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: keep leading zeros in a number field

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi all,

    How do you keep leading zeros in a number(6) field? I need the leading zeros to be stored. I have the folwoing code :
    UPDATE my_emp
    SET SSN = LPAD(emp_no,3,0)||9999;

    The script runs fine except the leading zeros are truncated because of the datatype. Is there a way to keep the leading zeros?

    Thanks,
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> select to_char(123, '099999') from dual;

    TO_CHAR
    -------
    000123

    SQL> col a format 0999999;
    SQL> select 123 a from dual;

    A
    --------
    0000123

  3. #3
    Hi Leonard,

    I don't think there is any way of storing leading zeros in number datatype in the database. While retrieving it however you can do so by using the format Shestakov specified. I would prefer keeping the field as a char/varchar2 type!

    Thanks,
    -Shyami

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Why need keep leading zeros, usually need only show this zeros in screen forms or
    print its in document.

Posting Permissions

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


Click Here to Expand Forum to Full Width