[RESOLVED] MySQL droping leading zeros!

spiritssight

Hello,

I have a simple question:

How do I keep the leading zero in a feild, I have a couple of feilds that start with 0 and MySQL drops them.

Sincerely,
Christopher

wilku

What is the type of a column you're using? If it's numerical, it will loose leading zeros. Change it to some textual type (char, varchar etc.).

bradgrafelman

[ EDITED - Changed my mind ]

spiritssight, is there a fixed width you're after? For example, if you wanted a field to be fixed at 8 digits and right-zero-padded to adjust the width, you could do:

ALTER TABLE yourTable CHANGE numberColumn numberColumn INT(8) ZEROFILL;

Thus, INSERT'ing a value of 1234 would result in 00001234 being stored in the column.

EDIT: Actually, we might want to know the application of these leading zeros first - what is the purpose of them? If it's simply for display purposes, then I would consider that styling that the database shouldn't be concerned with ([man]sprintf/man should be used instead).

tomhath

If the leading zeros are part of an ID or code, then the column should be char or varchar. Just because it only contains digits doesn't mean it's a number (e.g SSN, phone number, and zipcode are not numbers, they're character strings).

spiritssight

Thank You!

How do you know which one to pick?

The field is for a thing like a SSN but its called FIN / EIN = Fedral Indifcation Number

Sincerely,
Christopher

laserlight

What is the format of a FIN / EIN?

bradgrafelman

The answer to laserlight's question is most likely going to dictate what column type you should use. If it's an all-numeric, fixed-width, left zero-padded number, I would go back to my recommendation of an integer column with the "ZEROFILL" option (in MySQL at least - not sure about other DB's).

spiritssight

its a 9 digits and it may or may not have a zero to start

bradgrafelman

If you know it's a 9 digit number, then I would use an INT(9) ZEROFILL column as I originally suggested.

tomhath

Decide for yourself if the trade-off is worth the risk. An integer is smaller to store and faster to search, but not by much. I've seen it happen more than once that an ID like this can suddenly contain non-digits; maybe letters, or a hyphen in the middle. Or the length changes, so some might be nine digits and some might be ten or twelve.

My rule of thumb is that a number is something that can be used in arithmetic. Can you add two FederalIDs together? Multiply one by five? If not, it's a string, not a number. The fact that it's exactly nine digits this week doesn't make it a number.

bradgrafelman

tomhath wrote:
My rule of thumb is that a number is something that can be used in arithmetic.

Interesting point - never considered it like that before (at least, I never applied that line-of-thought to a situation before).

If you go with tomhath's suggestion, the "zero-padding" could easily be displayed:

echo sprintf("%010s", $row['FedID']); // where $row['FedID'] is the row in the DB