Zip Codes in lengths of 9,8,5 and4

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Zip Codes in lengths of 9,8,5 and4

Author

Topic

pizzo36
Starting Member

5 Posts

Posted - 2007-05-02 : 18:13:55

I am working with a table that has zip codes listed in lengths of 9,8,5 and 4 digits. The table is created this way and I have no way of changing the data outside of SQL. I am trying to get the last four digits off of all the zip codes so that I only have to work with zip codes in lengths of 5 and 4

Thanks,

Pizzo36

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 18:17:32

SELECT ZipCode, RIGHT(ZipCode, 4)
FROM Table1

This is all any can do since you haven't provided details about which 4 or 5 digits to work with.

Peter Larsson
Helsingborg, Sweden

pizzo36
Starting Member

5 Posts

Posted - 2007-05-02 : 18:27:43

Sorry, I should have clarified. The table does not allow for any leading zeros, so I would be looking for the first 5 or 4 digits of a zip code.

Example: 4444-0000
55555-0000
except without the dashes.

Sometimes the zip code is displayed as such:
4444
55555

I am looking for a way to identify the amount of digits representing the zip code and then trim off the appropriate amount based on length of the zip code.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 18:30:50

[code]select zipcode, case when pos > 0 then left(zipcode, pos - 1) else zipcode end as zipcode
from (
select zipcode, charindex('-', zipcode) as pos
from table1
) as d[/code]
Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-02 : 18:57:00

[code]
select
ZipCode,
ZipCode_5 =
case len(ZipCode)
when 4 then '0'+ZipCode
when 5 then ZipCode
when 8 then left('0'+ZipCode,5)
when 9 then left(ZipCode,5)
else null -- unexpected length
end
FROM
MyTable
[/code]

CODO ERGO SUM

pizzo36
Starting Member

5 Posts

Posted - 2007-05-03 : 11:54:40

Thanks, I just tried it and it worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources