99
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)