dmanisit
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or left
UPDATE tbl
Set fld = Left(fld,5)
UPDATE tbl
Set fld = Left(fld,5)
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:
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);
Hmm, lack of patience! ;-)
ASKER
Im sorry I may reopen the question just to award you some points.
@dmanisit,
No worries, one way or the other. ;-)
No worries, one way or the other. ;-)
Easy enough. :)
update {mytable}
set zipcode = substr (zipcode, 1, 5)
Good Luck,
Kent