Post reply

formatting the zipcode

  • May 19, 2008 at 8:43 am

    #70481

    DEMOG.ZIPCODE has zipcode data in it. Any zipode that is more than the five standard digits does not have a dash in it e.g. 477131230

    I need it to look like 47713-1230

    Any help would be appreciated.

    Thanks.

  • May 19, 2008 at 8:59 am

    #817232

    Not sure exactly what you are after but you can separate it into pieces using Substring and then build a new string with a '-' concatenated between the parts.

    declare @oldzip varchar(109)

    declare @newzip varchar(10)

    set @oldzip= '126011111'

    set @newzip = substring(@oldzip,1,5)+ '-' + substring(@oldzip,6,4)

    select @oldzip OLDZIP, @newzip NEWZIP -- show the results

    Toni

  • May 19, 2008 at 9:07 am

    #817244

    You can use the STUFF command

    declare @zip varchar(10)

    set @zip= '477131230'

    select @zip,stuff(@zip,6,0,'-')

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • May 19, 2008 at 9:15 am

    #817252

    Thank you.

    I don't know too much about variables.

    set @zip = (select zipcode from DEMOG) does not work because there is more than one result.

    How would I go about updating all zipcodes in DEMOG ?

    Mark (5/19/2008)

    You can use the STUFF command

    declare @zip varchar(10)

    set @zip= '477131230'

    select @zip,stuff(@zip,6,0,'-')

  • May 19, 2008 at 9:19 am

    #817256

    adam (5/19/2008)

    Thank you.

    I don't know too much about variables.

    set @zip = (select zipcode from DEMOG) does not work because there is more than one result.

    How would I go about updating all zipcodes in DEMOG ?

    Mark (5/19/2008)

    You can use the STUFF command

    declare @zip varchar(10)

    set @zip= '477131230'

    select @zip,stuff(@zip,6,0,'-')

    update DEMOG

    set zipcode=stuff(zipcode,6,0,'-')

    where len(zipcode)>5

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • May 19, 2008 at 9:22 am

    #817261

    Ah, I forgot about LEN.

    Thanks for all your help.

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

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