TeddyR TeddyR - 1 month ago 15
MySQL Question

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

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

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:

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

Or you could have MySQL pad it for you:

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
Comments