Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

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
0
dmanisit
Asked:
dmanisit
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:


Easy enough.  :)


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


Good Luck,
Kent
0
 
Ephraim WangoyaCommented:
its SUBSTRING

update mytable
set zipcode = substring(zipcode, 1, 5)
0
 
Andre412Commented:
or left

UPDATE tbl
Set fld = Left(fld,5)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now