Microsoft SQL Query Zip Code Clean up

I everyone,

I have a table that has a field for zipcodes, some of these zipcodes are 5 digit and others are the 9 digit, I would like to go and strip off everything except the first 5 digits, can someone help please?

thank you
dmanisitAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
its SUBSTRING

update mytable
set zipcode = substring(zipcode, 1, 5)
0
 
Kent OlsenData Warehouse Architect / DBACommented:


Easy enough.  :)


update {mytable}
set zipcode = substr (zipcode, 1, 5)


Good Luck,
Kent
0
 
Andre412Commented:
or left

UPDATE tbl
Set fld = Left(fld,5)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
8080_DiverCommented:
How about the following?

SQL1:
It is entirely possible to have leading spaces in VarChar fields

SQL2:
If you have stored the ZipCodes as Integers, then you have to convert them to VarChars, extract the left 5 characters, and then convert them back.

SQL1:
UPDATE yourtable
SET ZipCode = LEFT(LTRIM(ZipCode), 5);

SQL2:
UPDATE yourtable
SET ZipCode = LEFT(CONVERT(VarChar(9), ZipCode), 5);

Open in new window

0
 
8080_DiverCommented:
Hmm, lack of patience!  ;-)
0
 
dmanisitAuthor Commented:
Im sorry I may reopen the question just to award you some points.
0
 
8080_DiverCommented:
@dmanisit,

No worries, one way or the other. ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.