Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Trim 9 digit zip codes to 5 digit and move remaining 4 digits to new column

    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.

  • #2
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    Code:
    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.
    Last edited by Old Pedant; 09-13-2011 at 01:54 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    ktrollinger (09-13-2011)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    Quote Originally Posted by mic2100 View Post
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,648 Times in 4,610 Posts
    If your 9-digit zip codes are in a LONGINT field, then you can do it via arithemetic, instead, of course:
    Code:
    ALTER TABLE yourtablename ADD zipplus4 INT;
    
    UPDATE yourtablename 
    SET zipplus4 = existingzip % 10000,  existingzip = FLOOR(existingzip/1000) 
    WHERE existingzip > 99999;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    removed - missed the greater than ">5" statement
    Last edited by ktrollinger; 09-13-2011 at 03:44 AM.

  • #7
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    UPDATE yourtablename
    SET zipplus4 = RIGHT(existingzip,4), existingzip=LEFT(existingzip,5)
    WHERE LENGTH(existingzip) > 5;

    Bang On Perfect!
    Last edited by ktrollinger; 09-13-2011 at 03:48 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •