I'm querying a very large table (over 3M records) in MySQL that has a category_id, subcategory_id and zipcode. The zip may or may not be 10 characters in the db.
The purpose is to get all the cat/subcat items w/in a certain radius of the specified zip. I have a function that returns a list of 5-digit zips for one specified. This list is then fed to my query like so...
WHERE cat_id = 1
AND subcat_id = 5
AND LEFT(zip,5) IN (11111,22222,33333,44444,55555)
You should have a column with the normal 5 digit zip and column with all of the extra digits and let SQL handle it normally. There are ways you could do what your talking about, but this is by far the most efficient solution.