Ok, I have TableA which stores a postal code along with other info... The zipcode is either 6 or 7 digits/characters long (depending on country)... and I need to compare it to a 3 or 6 character postal_code I have in Table B with city and state.
so I wrote a function using strlen ( let's call it zipChange() )... anyway, it does the work I need it to by taking the 6 or 7 character zip and returning it as a 3 or 6 character zip.
so in the query I need to have a WHERE TableA.postal_code = TableB.postal_code... but I need to run TableA.postal_code through the function first...
help! Thanks.
Well if you wrote the function in PHP, you'll have to retrieve all of TableA's postal_codes, apply your conversion function, and then perform a separate SELECT to get the appropriate data from TableB.
If you instead do the conversion in the SQL query itself (or write a SQL function) that does the same conversion, you could keep the single query.
Can you show us the function you created?
function zipChange($zip) { if(strlen($zip) == 6){ return $zip; } if (strlen($zip) == 7){ return substr($zip, 0, 3); } }
Simple enough - MySQL has functions that mirror those you've used in PHP:
IF(LENGTH(TableA.postal_code) = 6, TableA.postal_code, SUBSTRING(TableA.postal_code, 3))
ok, I think I got it... but rather than
I used:
IF(LENGTH(TableA.postal_code) = 6, TableA.postal_code, LEFT(TableA.postal_code, 3))
By the way, THANKS! Much appreciated..
fontener;10950396 wrote:I used: IF(LENGTH(TableA.postal_code) = 6, TableA.postal_code, LEFT(TableA.postal_code, 3))
Makes more sense - I was just trying to translate the PHP into MySQL too literally. :p
