Solved

Microsoft SQL Query Zip Code Clean up

Posted on 2010-11-19
7
627 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 45

Expert Comment

by:Kdo
ID: 34176439


Easy enough.  :)


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


Good Luck,
Kent
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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