Post reply

help w/ trimming column

  • July 11, 2005 at 9:33 am

    #164308

    I need help updating a table of zip codes... The table currently has zip codes in the 9 digit format however the last 4 numbers are usally zero's (ex. 191280000). I want to update my table so the last 4 digits of the zip code are dropped (make 191280000 into 19128).

    This is an example of what I have so far but dosen't work...

    update table set column = where trim(trailing '0000' from '%0000')

    Thanks,

    Meredith, a newbie

  • July 11, 2005 at 9:39 am

    #572972

    is the data in a varchar column or an int?

    Update table set column = left(Column, len(Column) - 4) where Right(column, 4) = '0000'

  • July 11, 2005 at 9:58 am

    #572986

    varchar...

    I keep getting a missing right parenthesis error for some reason.

    Thanks

  • July 11, 2005 at 10:01 am

    #572988

    What statement are you using?

  • July 11, 2005 at 11:17 am

    #573013

    I'm using your statment... I'm not sure if I have an extra space some where or what, it looks like all of your parenthesis are closed.

  • July 11, 2005 at 11:24 am

    #573019

    Please paste the exact statement you are using. My statement works right away (besides the foridden use of the reserved works table and column). It's most likely a simple typo you just don't see.

  • July 11, 2005 at 11:31 am

    #573022

    Why not use UPDATE table SET Column = LEFT(Column, 5) ?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 11, 2005 at 11:49 am

    #573038

    Because I wasn't assuming that there was only us zip codes .

    It's hard to tell when you don't have all the info...

  • July 11, 2005 at 11:49 am

    #573037

    Here's the first version that I tried...

    update mci_geocodable set zipcode_2 = left(zipcode_2,9(zipcode_2) - 4) where right(zipcode_2,4)='0000'

    Question??? what is this sql statement supposed to do???  1) create a new column and and populate it. 2) edits and update the exsisting column and contents. or  3) takes the contents from a source column and edits and populates a exsisting empty column?

    Thanks...

  • July 11, 2005 at 11:51 am

    #573044

    Remove the part in red

    update dbo.mci_geocodable set zipcode_2 = left(zipcode_2,9(zipcode_2) - 4) where right(zipcode_2,4)='0000'

  • July 11, 2005 at 11:51 am

    #573043

    Sometimes you have to look around the error to actually find it .

  • July 11, 2005 at 11:55 am

    #573047

    ..to jump in...it updates the existing column...if (as AJ suggests) you have only US zipcodes, you can replace your statement with:

    update dbo.mci_geocodable set zipcode_2 = left(zipcode_2, 5) where right(zipcode_2,4)='0000'







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 11, 2005 at 5:58 pm

    #573176

    I apologize for my narrow thinking on this one Remi.  I ASSumed US-ONLY based on the sample.  You are absolutely correct that my sample WOULD NOT wokr in Europe.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 11, 2005 at 6:01 pm

    #573177

    Actually I was reffering to canada... but if you say europe too I'll believe you .

  • July 11, 2005 at 6:02 pm

    #573178

    So when are you crossing the big K?

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply