Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Baton Rouge, LA
    Posts
    2

    Unhappy Unanswered: splitting 9 digit zip codes

    hey all
    i am stuck with this little problem
    I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'

    is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?

    thanks
    Last edited by sotaib1; 07-06-04 at 12:49.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Assuming that the new column already exists (FullZip - original mix, Zip4 - the last 4 digits of the 9):

    update tbl
    set Zip4 = reverse(cast(reverse(FullZip) as char(4))),
    FullZip = cast(FullZip as char(5))
    where datalength(FullZip) >= 9
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start with:
    Code:
    UPDATE myTable
       SET zip = Left(zip, 5)
    ,     upl = Replace(SubString(zip, 6, 2000), '-', '')
       WHERE  5 < Len(zip)
    Keep in mind that you'll probably want to decorate to taste as you see fit.

    -PatP

  4. #4
    Join Date
    Jun 2004
    Location
    Baton Rouge, LA
    Posts
    2

    thanks

    thank you all very much
    it worked great

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Which one worked great?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    who cares...mail me some gumbo!

    "I will play (or code sql) for gumbo"

    I Will Play For Gumbo

    I don't smoke, I don't shoot smack
    But I got a spicy monkey ridin' on my back.
    Don't eat beignets, too much sugar and dough,
    But I will play for gumbo
    Yes, I will play for gumbo

    It started at my Grandma's kitchen by the sea,
    She warned me when she told me "son the first one's free"
    It hit me like a rock or some Taikwondo,
    Cause I will play for gumbo
    Oh yea, I will play for gumbo.
    Chorus
    A piece of French bread
    With which to wipe my bowl,
    Good for the body.
    Good for the soul.
    It's a little like religion
    And a lot like sex.
    You should never know
    When you're gonna get it next.
    At midnight in the quarter or noon in Thibadeaux
    I will play for gumbo
    Yes, I will play for gumbo.

    I'm not talkin' quesadillas or a dozen Krispy Kremes,
    Or a pound of caviar that's a rich man's dream.
    No banana split or fillet of pompano.
    No, I will play for gumbo,
    Yeah, I will play for gumbo
    Chorus
    Maybe it's the sausage or those pretty pink shrimp
    Or that popcorn rice that makes me blow up like a blimp.
    Maybe it's that voodoo from Marie Leveaux,
    But I will play for gumbo
    Yeah, I will play for gumbo

    The sauce boss does his cookin' on the stage,
    Stirrin' and a singing for his nightly wage.
    Sweating and frettin' from his head to his toe,
    Playin' and swayin' with the gumbo
    Prayin' and buffetin' with the gumbo
    Chorus
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Brett writes poems...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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