...

View Full Version : Trim 9 digit zip codes to 5 digit and move remaining 4 digits to new column



ktrollinger
09-13-2011, 12:37 AM
Hi,

I have a table with a mix of 5 and 9 digit zip codes. I'd like to trim the 9 digit zips located in the zip column and move the remaining 4 digits in a column named zip4.

Any help is appreciated.

mic2100
09-13-2011, 12:43 AM
you could try something like

http://forums.mysql.com/read.php?115,209840,209840

but i have never user SQL like that before so i can't confirm that it will work. It would be easier to create a PHP script to handle it.

Old Pedant
09-13-2011, 01:48 AM
ALTER TABLE yourtablename ADD zipplus4 VARCHAR(4);

UPDATE yourtablename
SET zipplus4 = RIGHT(existingzip,4), existingzip=LEFT(existingzip,5)
WHERE LENGTH(existingzip) > 5;

That *assumes* that your 9 digit zip codes are indeed in a VARCHAR field.

It will handle *EITHER* "12345-9876" or "123459876" (that is, with or without the dash).

It also assumes you are using a database that has the LEFT and RIGHT functions. Since you posted in the MYSQL forum, which does, we will assume you are okay.

Old Pedant
09-13-2011, 01:51 AM
you could try something like

http://forums.mysql.com/read.php?115,209840,209840

HUH? That's nothing at all like what he needs.


It would be easier to create a PHP script to handle it.

Easier than a ONE LINE sql query? I doubt it seriously. (I say one line, because you would need the ALTER TABLE or equivalent no matter if you do it in SQL or in PHP. So a single very simple UPDATE is all that is needed in SQL. Contrast that to the ugly stuff you'd have to do to write it in PHP.

Old Pedant
09-13-2011, 01:54 AM
If your 9-digit zip codes are in a LONGINT field, then you can do it via arithemetic, instead, of course:


ALTER TABLE yourtablename ADD zipplus4 INT;

UPDATE yourtablename
SET zipplus4 = existingzip % 10000, existingzip = FLOOR(existingzip/1000)
WHERE existingzip > 99999;

ktrollinger
09-13-2011, 02:37 AM
removed - missed the greater than ">5" statement

ktrollinger
09-13-2011, 03:42 AM
UPDATE yourtablename
SET zipplus4 = RIGHT(existingzip,4), existingzip=LEFT(existingzip,5)
WHERE LENGTH(existingzip) > 5;

Bang On Perfect!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum