?
Solved

Microsoft SQL Query Zip Code Clean up

Posted on 2010-11-19
7
Medium Priority
?
714 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:dmanisit
7 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 34176439


Easy enough.  :)


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


Good Luck,
Kent
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 34176501
its SUBSTRING

update mytable
set zipcode = substring(zipcode, 1, 5)
0
 
LVL 4

Expert Comment

by:Andre412
ID: 34176929
or left

UPDATE tbl
Set fld = Left(fld,5)
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34176942
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34176945
Hmm, lack of patience!  ;-)
0
 

Author Comment

by:dmanisit
ID: 34176962
Im sorry I may reopen the question just to award you some points.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34191472
@dmanisit,

No worries, one way or the other. ;-)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question