Zip Code Converstion 1

[mttorpy]

I am trying to change the zip code information in our SQL2000 DB, currently we are storing 10 digit zip codes. I am able to isolate the offending zip codes and change them to 5 digits but how do I do an update the table?


select id_acc, substring(c_zip, 1, 5) as c_zip
--into NEW_t_av_contact
from t_av_contact
where len(c_zip) > 5 and
c_country = 421

 

[SQLSister]

Do you want to change all teh zips to 5 digit or just some specific ones?

Update table1
set zip = left(zip, 5)

Will do all

Update table1
Set zip = left(zip, 5)
where (some condition)

YOu could use substring instead of left if you want.

 

[mttorpy]

Your a goddess, thank you so much.

I will use the condition as I have some zips that are Canadian.
so...

Update t_av_contact
Set zip = left(zip, 5)
--or Set zip = substring(zip, 1, 5)
where len(c_zip) > 5 and
c_country = 421

 

[ESquared]

Are you sure you don't want to capture the +4 part into another column, first, 'just in case?'