Post reply

Help a newbie....please???

  • February 27, 2004 at 11:54 am

    #84302

    I need to update all the zipcodes in one of the tables in my database.  Right now some are 5 digit zipcodes and the others are 5 + 4 zipcodes.  I need to update it so that it stips off the +4 zipcodes and just leaves me the 5 digits.  I am not sure what I need to do to accomplish this.  I am very new to sql and any help would be appreciated.

    Thankx

  • February 27, 2004 at 12:27 pm

    #496275

    To build the exact query I may need more info

    But here is how you do it :

    Update TableName SET FldZipcode = CASE WHEN Len(LTRIM(RTRIM(FldZipCode))) = 5 THEN FldZipCode ELSE Left(LTRIM(FldZipCode),5) END

    If you need to make sure before you update the table Run this:

    SELECT FldZipCode as InitialValue , CASE WHEN Len(LTRIM(RTRIM(FldZipCode))) = 5 THEN FldZipCode ELSE Left(LTRIM(FldZipCode),5) END AS NewValue

    From TableName

     


    * Noel

  • February 27, 2004 at 12:30 pm

    #496276

    see a sample

    Col1 -- is the unique column

    col2 is the zip code column..

    update table2 set col2 = a.col2 from table2 join (select col1,substring(col2,1,5) as col2 from table2 )a on table2.col1=a.col1

    Thanks

    Linto

  • February 27, 2004 at 12:32 pm

    #496277

    Worked great, thanks for your help 400 Posts!......

  • February 27, 2004 at 12:39 pm

    #496279

    Just call him noeld

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 27, 2004 at 12:51 pm

    #496283

    Right


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

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