aspose file tools *
The moose likes JDBC and the fly likes Dropping leading zeros on numeric field Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dropping leading zeros on numeric field" Watch "Dropping leading zeros on numeric field" New topic
Author

Dropping leading zeros on numeric field

Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Hello! I am using a DB2 database which has a zip code stored in a table defined as NUMERIC(5,0).
When I select the data from this table, if the zip code starts with a zero, it seems no matter what I try, it always drops the leading zero.
Other than defining the field in the table as a Char field, how can I bring back the leading zero to my app?
Thanks for any help!
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Hi
I use LPAD function in Oracle. Is there any equivalent function in DB2.

-Sainudheen
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

it seems no matter what I try, it always drops the leading zero.

It will do so because numbers are stored in that manner. Leading zero do not add value to a number.


Groovy
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Why do people store zip codes as numbers. They aren't really numbers even though they just happen to be made up of digits. You don't add zip codes together or divide them to get some meaningful figure. And you can't store Canadian postal codes in the same field.
That being said, I think you can use the DIGITS function in DB2 to return a number with lead zeroes.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Thanks for the tip on the function DIGITS in DB2.
I also have to say I agree with you on storing zip codes as numbers.
Our current system is rather old, so I'm not sure who did it that way, which is why we are creating a new one.
It's kind of a pain, because our current system never allowed a country to be keyed, but our new one does, which requires an alphameric zip code.

Lucky me, I'm the one who gets to make the old work with the new.
I'll be sure to make my zip codes alphameric so the next programmer who has to work with my stuff in 50 years doesn't yell at me!! (Hopefully, I'm retired by then!)
Thanks again!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dropping leading zeros on numeric field