Recently, I helped a client import a large set of addresses into a location plugin for WordPress. The import mainly went smoothly, but we noticed some issues when searching in areas with zip codes leading with one or two zeroes. The addresses weren’t coming up as they should.

After examining some of the imported addresses, we realized that all of the leading zeroes were being stripped, and we could no longer search by those zip codes. I’m going to give a brief overview of why this happened, and how I solved it. Hopefully it helps if you need to make this kind of update to a WordPress database too!

Why is this happening?

Some programs “helpfully” strip leading zeroes from numbered cells, including Excel, Numbers, and Google Sheets. This means that 04102 in Portland, Maine becomes 4102, which isn’t a zip code in the US.

The same could happen upon import into the database, depending on how the import is done. In either case, I’m working with an import that’s already complete, as opposed to having caught this issue before the addresses were added to the site. I don’t want to remove all other relevant content just to import again and fix these zip codes, so I’m going to go directly to the database to solve the problem.

How to fix the missing zeroes

There are several ways to add the zeroes back, but most places that you search will suggest changing the datatype of the zip code column, which doesn’t help when it’s in WordPress where we can’t modify that when there is other info stored in the same place. Plus some zip codes have the full nine digit route number depending on where the data was taken from, and some are postal codes from Canada and other countries that don’t follow the same pattern.

In this particular case, we know what we’re looking for (postmeta with a key of wpsl_zip, and we know where it’s at (the wp_postmeta table). If you connect to the MySQL database through PHPMyAdmin or an external application you can run the following query to see how many zip codes stored have fewer than five digits:

Important: Always make a backup of your database before doing any of the changes below!

SELECT
    *
FROM
    `wp_postmeta`
WHERE
    `meta_key` = 'wpsl_zip' AND LENGTH(`meta_value`) < 5Code language: SQL (Structured Query Language) (sql)

What we’ve told the database to do, is to “select all rows from the wp_postmeta table that have a meta_key of ‘wpsl_zip’, and that have a meta_value of less than five characters in length”.

It’s important to ignore rows that already have a value of five or more characters, as LPAD will trim them to fit five characters otherwise. We don’t want that, just the ones that are too short.

The above will return all of the rows that match the query, so that we can review them and confirm that they are indeed the addresses that we want to update.

Now that we’ve identified how many there are (89 in this case), the following MySQL command will update those zipcodes using LPAD to add a left padding of 0’s until the meta_value is five characters. Values that are already five characters or larger are ignored.

UPDATE
    `wp_postmeta`
SET
    `meta_value` = LPAD(`meta_value`, 5, 0)
WHERE
    `meta_key` = 'wpsl_zip' AND LENGTH(`meta_value`) < 5Code language: SQL (Structured Query Language) (sql)

You’ll see that the WHERE clause is the same, since we already confirmed that we had the right records to change before. What we’ve done differently with this query is to say that we want to make updates to the wp_postmeta table by setting the meta_value of the rows that we selected to have exactly five characters, and that if they have fewer than five characters, to left pad them with 0’s.

Summary

To review, the MySQL function LPAD works like this:

LPAD(
    "cell that we want to change",
    "final cell string length",
    "what to use to left pad the cell if needed"
)Code language: SQL (Structured Query Language) (sql)

I hope that helps save you spending the same time that it took me to find the problem that I had and to come up with a solution!

Republish