Zip-Code Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Zip-Code Query

Author

Topic

dextrous
Starting Member

15 Posts

Posted - 2009-07-30 : 03:15:27

Hello,
I have a huge database of US Zip-codes (Zip as well as zip+4).

Each row has some data about the zip-code (income, population, total, etc.). There's a cluster index on the zipcode.

Right now the use for this db is with a simple application I'm developing:
When a user supplies a zipcode in a csv file, the data is read and the output appends the zip code information from the db onto the file.

So, if user gives 94122-1111, the query matches 94122-1111 in the db and pastes all relevant information into the file.

The problem is when a user gives 94122-1108...and there's no match for it as we may not have information for that zipcode because its new or just an office zipcode, etc. In these instances, I want to display the closest numeric match instead. So, if we we have 94122-1111 but not 94122-1108, I want to display that.

Is there an easy way to find the closest numeric match?

Thanks

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-30 : 04:10:49

The closest thing to our request I have ever seen is Peso with his geocoding :)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111356

may be it will help

dextrous
Starting Member

15 Posts

Posted - 2009-07-30 : 16:23:18

Hi,
What if we considered them as numbers, "941221111"...is it possible to find the "closest match" to numerical values?

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 16:48:41

quote:
Originally posted by dextrous

Hi,
What if we considered them as numbers, "941221111"...is it possible to find the "closest match" to numerical values?

Sure...here's a way to find to closest one before and the closest one after

cut and paste the code to see it work


CREATE TABLE #zip(zip4 varchar(10))
GO

INSERT INTO #zip(zip4)
SELECT '94122-1099' UNION ALL
SELECT '94122-1100' UNION ALL
SELECT '94122-1111'  UNION ALL
SELECT '94122-1112' UNION ALL
SELECT '94122-1113'
GO

SELECT * FROM #zip
GO

DECLARE @inputzip int
SET @inputzip = '941221108'

SELECT MAX(CONVERT(int,REPLACE(zip4,'-',''))) AS zip_BEFORE
  FROM #zip
WHERE CONVERT(int,REPLACE(zip4,'-','')) < @inputzip
UNION ALL
SELECT MIN(CONVERT(int,REPLACE(zip4,'-',''))) AS zip_AFTER
  FROM #zip
WHERE CONVERT(int,REPLACE(zip4,'-','')) > @inputzip
GO

DROP TABLE #zip
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dextrous
Starting Member

15 Posts

Posted - 2009-07-30 : 17:06:39

Great, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources