how to remove trailing zeros from zip codes

Question

Post reply

how to remove trailing zeros from zip codes

sbarlow

Old Hand

Points: 340
More actions
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

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

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

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.

sbarlow Old Hand Points: 340 More actions · Answer 2

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 3

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

sbarlow Old Hand Points: 340 More actions · Answer 4

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

Thanks

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 5

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.

sbarlow Old Hand Points: 340 More actions · Answer 6

sbarlow

Old Hand

Points: 340

February 13, 2012 at 4:46 pm

#1446582

That did it thanks for your help