dougrdotnet

I ran across an issue with an app I’m working where a zip code text box was accepting invalid entries.  The validation was using the db for a table of zip code ranges and comparing the user’s entry to valid ranges of codes in the db.  I didn’t at first get why a user could enter 500 for New York, as opposed to 00500, and it would validate.  I went and had a look into the table and indeed the range for New York was between 500 and 599, rather than 00500 and 00599.

Solution: Write an ALTER TABLE query to change the length required in the column to 5, add ZEROFILL, and add UNSIGNED to the table structure.

By altering the table for ZEROFILL, this will automatically add zeros before any entry which has less numerals than 5, as specified in the query.  MySQL adds UNSIGNED to a column that has a ZEROFILL attribute.  From the MySQL Docs:

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

Here is the query to alter a table in order to change the table structure for ZEROFILL and ensure 5 digits:

ALTER TABLE [table name] MODIFY COLUMN [column name] INT(5) ZEROFILL UNSIGNED;

After altering the table the zip code validation began working as expected.  Sometimes, its just not in the code and one has to have a look around for other possibilities.  Hope this helps you out.