Post reply

how to remove trailing zeros from zip codes

  • February 13, 2012 at 3:42 pm

    #251548

    I have a column that holds zip codes I need to remove 4 trailing zeros how can I do this? the zip codes are store like this 131640000 i need to remove the last four zeros.

    Thanks

  • February 13, 2012 at 4:09 pm

    #1446573

    Are they all 9 digits long and all have 4 zeros at the end? If so,

    substring(zip, 1, 5)

    would work. If you post some code with some sample data of what you have if it is messier than that we can get you a better solution.

  • February 13, 2012 at 4:14 pm

    #1446574

    If i run this select * from person WHERE len(zip) > 5 I get this result what I need is an update statement to remove the zeros

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

  • February 13, 2012 at 4:16 pm

    #1446575

    sbarlow (2/13/2012)

    If i run this select * from person WHERE len(zip) > 5 I get this result what I need is an update statement to remove the zeros

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    134930000

    Try

    select substring(zip, 1, 5)

    from person

    where len(zip) > 5

  • February 13, 2012 at 4:20 pm

    #1446577

    that gives me what i need what is the best way to change that to an update statement

    Thanks

  • February 13, 2012 at 4:24 pm

    #1446578

    If you are sure you want all the zips length greater than 5 then something like this should work.

    update p

    set p.zip = substring(p.zip, 1, 5)

    from person p

    where len(zip) > 5

    You could also use left(zip, 5) as well does the same thing in this situation.

  • February 13, 2012 at 4:46 pm

    #1446582

    That did it thanks for your help

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

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