Preserving leading zeros

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
steve9876
Forum Newbie
Posts: 13
Joined: Tue Jul 21, 2009 7:34 pm

Preserving leading zeros

Post by steve9876 »

I've just created my first mysql database, and find that in a CHAR field that will be all digits, leading zeros are truncated.

I want to keep leading zeros. Is there another datatype or option that will do that?
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Preserving leading zeros

Post by tr0gd0rr »

It looks like you want an INT field with ZEROFILL : "For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004."
steve9876
Forum Newbie
Posts: 13
Joined: Tue Jul 21, 2009 7:34 pm

Re: Preserving leading zeros

Post by steve9876 »

Thanks, but I came up with another solution: instead of generating a random number between 1000001 and 1999999 (for 6 digits), I generate one between 1100001 and 1999999. (Or maybe I could have just used 100001 - 999999.)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Preserving leading zeros

Post by jayshields »

Correct me if I'm wrong, but CHAR fields shouldn't strip leading zeros, but anyway, if you want to use any field which strips leading zeros, and you know how many digits the integer should be, you can just pad it out on/after retrieval.
Post Reply