MySQL - how to front pad zip code with "0"?

Question

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up.

The clean zip code data is when I have all 5 digits for a zip code (e.g. "90210").

But for some reason, I noticed in my database that for zipcodes that start with a "0", the 0 has been dropped.

So "Holtsville, New York" with zipcode "00544" is stored in my database as "544"

and

"Dedham, MA" with zipcode "02026" is stored in my database as "2026".

What SQL can I run to front pad "0" to any zipcode that is not 5 digits in length? Meaning, if the zipcode is 3 digits in length, front pad "00". If the zipcode is 4 digits in length, front pad just "0".

UPDATE:

I just changed the zipcode to be datatype VARCHAR(5)

Answer 1

Store your zipcodes as CHAR(5) instead of a numeric type, or have your application pad it with zeroes when you load it from the DB. A way to do it with PHP using sprintf():

echo sprintf("%05d", 205); // prints 00205 echo sprintf("%05d", 1492); // prints 01492 

Or you could have MySQL pad it for you with LPAD():

SELECT LPAD(zip, 5, '0') as zipcode FROM table; 

Here's a way to update and pad all rows:

ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything 

Answer 2

You need to decide the length of the zip code (which I believe should be 5 characters long). Then you need to tell MySQL to zero-fill the numbers.

Let's suppose your table is called mytable and the field in question is zipcode, type smallint. You need to issue the following query:

ALTER TABLE mytable CHANGE `zipcode` `zipcode`     MEDIUMINT( 5 ) UNSIGNED ZEROFILL NOT NULL; 

The advantage of this method is that it leaves your data intact, there's no need to use triggers during data insertion / updates, there's no need to use functions when you SELECT the data and that you can always remove the extra zeros or increase the field length should you change your mind.