KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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)
Tags (comma-separated)
Save Edits
Cancel