i need to do a reverse lookup on zip and get city and state for us and canada.

Question

i need to do a reverse lookup on zip and get city and state for us and canada.

Snargables

SSCrazy Eights

Points: 8855
More actions
December 16, 2011 at 7:27 am

#253660

At this point i havent done anything. I'm assuming i'll have to bring down a file of sort and so on. Can anyone point me in the right direction here?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 1

It may be possible to do what you're after in a query, but without any sample data or DDL it makes things difficult for us.

Try this for help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sounds like something that Excel would eat for breakfast though using a simple VLOOKUP??

SQL SERVER Central Forum Etiquette[/url]

Snargables SSCrazy Eights Points: 8855 More actions · Answer 2

Let me rephase this. Does anyone know where i can get a file that has all the city, state, zips? Preferably a free one? If not does anyone have a suggestion as to where i can get one cheap? Anyone have input as far as pros and cons between the different peolpe who provide sed files? Are some more accurate than others ETC.

Or does sql server allow this now internally? I know the answer is no to this one but whatever. I had to try just in case cause i know they have GEOMETRY and GEOGRAPHY data types in sql server 2008..

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 3

Ahhhh I see...

I assume you're in the US? There's a fantastic one for the UK based on an ordinance survey. That I can link you to if needed?

Try this site perhaps: http://federalgovernmentzipcodes.us/

There are a couple of databases you can try there..

SQL SERVER Central Forum Etiquette[/url]

MarcoAntonio SSC Veteran Points: 253 More actions · Answer 4

I needed the same data you did a couple years ago, but we bought something specifically for MS Dynamics Ax.

However, this site has csv files it appears for both the U.S. and Canada - http://www.populardata.com/downloads.html.

Lowell SSC Guru Points: 323518 More actions · Answer 5

at least for US data, Here's a copy of one of mine; 42K zip codes, mapped to Cty/County/State;

CITYDATA_INSERTS.txt

populating city/county/state if you know the zip is no problem, but

if you do it the other way, updating zip code because you know the city, remember that theres more than one zip to a city, so grabbing thats not going to be accurate.

enjoy!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Lowell (12/16/2011)
at least for US data, Here's a copy of one of mine; 42K zip codes, mapped to Cty/County/State;
CITYDATA_INSERTS.txt
populating city/county/state if you know the zip is no problem, but
if you do it the other way, updating zip code because you know the city, remember that theres more than one zip to a city, so grabbing thats not going to be accurate.
enjoy!

Sweet Lowell. Added that to my collection of random data things. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Snargables SSCrazy Eights Points: 8855 More actions · Answer 7

Snargables

SSCrazy Eights

Points: 8855

December 16, 2011 at 9:44 am

#1422609

Excellent guys, thanks!!!!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

JimbobsQL (12/16/2011)
It may be possible to do what you're after in a query, but without any sample data or DDL it makes things difficult for us.
Try this for help: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Sounds like something that Excel would eat for breakfast though using a simple VLOOKUP??

Gosh... why would you have someone do such a thing in Excel? SQL Server will do it just fine and with a whole lot less fanfare.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

I had similar requirements for one assignment & luckily my customers bear the pain (to provide the SEED data).

However in our investigation we found something interesting & brought into attention of customer.

‘Zip code Changes over a period of time’. USPS has some algorithm which consider the population distribution per square mile (or some distance unit) and when identify the need it adds / merges zip codes. So any logic you are planning to build around zip codes is at risk.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

Another thing to remember (in the U.S. at least) is that zip codes can also span city boundaries and even county boundaries. It's not a 1-1 relationship. Zips are not tied to any governmental entity. They are simply distribution areas for the postal service.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

The Dixie Flatline (12/19/2011)
Another thing to remember (in the U.S. at least) is that zip codes can also span city boundaries and even county boundaries. It's not a 1-1 relationship. Zips are not tied to any governmental entity. They are simply distribution areas for the postal service.

With all due respect, I tend to disagree here. Postal Services belong to Government (country specific) they can’t cross the country boundaries. The same is applicable to states as well but I am not sure here (may be one incidence, very rare).

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 12

The Dixie Flatline said county boundaries, not country boundaries.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

Cath Trimble (12/20/2011)
The Dixie Flatline said county boundaries, not country boundaries.

Thanks for reminding me to visit Ophthalmologist :doze:

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

Whups. What I meant to say was country foundries. They even deliver mail to rural metal casters.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills