MySQL – how to front pad zip code with “0”?

by

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

More Related Contents:

  1. single fixed table with multiple columns vs flexible abstract tables
  2. MySQL query finding values in a comma separated string
  3. In MySQL queries, why use join instead of where?
  4. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  5. How to create multiple one to one’s
  6. Strings as Primary Keys in SQL Database [closed]
  7. Can table columns with a Foreign Key be NULL?
  8. A beginner’s guide to SQL database design [closed]
  9. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  10. MySQL not using indexes with WHERE IN clause?
  11. How many rows in a database are TOO MANY?
  12. SQLite – ORDER BY RAND()
  13. SQL – How to transpose?
  14. Primary key or Unique index?
  15. How to design a database for User Defined Fields?
  16. How to delete Duplicates in MySQL table
  17. Composite Primary key vs additional “ID” column?
  18. Database EAV Pros/Cons and Alternatives
  19. Best database field type for a URL
  20. Max length for client ip address [duplicate]
  21. Query that ignore the spaces
  22. How to Optimize Queries in a Database – The Basics
  23. Composite Primary Keys : Good or Bad?
  24. Create date from day, month, year fields in MySQL
  25. SQL: Using NULL values vs. default values
  26. What does ON [PRIMARY] mean?
  27. How do I list all the columns in a table?
  28. Two single-column indexes vs one two-column index in MySQL?
  29. Relational Data Model for Double-Entry Accounting
  30. VARCHAR as foreign key/primary key in database good or bad?

Leave a Comment