Virginia Web Designers: Joomla, Drupal, Open-Source, CSS, and PHP Northern Virginia / Maryland / Washington DC web designers

Using MySQL to insert missing text depending on field length

Suppose you have a MySQL database table containing US 5-digit zip codes, except some of them are just three or four digits long because they had leading zeros, and those sometimes get dropped. Here’s how to add them back in;

For 3-digit zip codes:

UPDATE zipcodes SET zip = CONCAT(’00′, zip) WHERE LENGTH(zip) = 3

This will turn ’123′ into ’00123′.

For 4-digit zip codes:

UPDATE zipcodes SET zip = CONCAT(’0′, zip) WHERE LENGTH(zip) = 4

This will turn ’1234′ into ’01234′.