• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dropping leading zeros on numeric field

 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I use LPAD function in Oracle. Is there any equivalent function in DB2.

-Sainudheen
 
Pradeep bhatt
Ranch Hand
Posts: 8933
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic