ZipCode lookup - 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)

ZipCode lookup

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 07:43:50

Hi, I need to filter a table on zipcode.
The zipcodes are of this format:
1234AB

Now, I need to filter on the first 4 numbers.
So I would need something like
Zipcode between 1234 and 4567

How can I do this?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 07:49:20

SELECT * FROM table WHERE SUBSTRING(zipcodes,1,4)+0 BETWEEN 1234 AND 4567

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 07:51:38

[code]SELECT * FROM Table WHERE LEFT(ZipCode,4)*1 BETWEEN 1234 AND 4567[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 07:54:34

quote:
Originally posted by visakh16

SELECT * FROM Table WHERE LEFT(ZipCode,4)*1 BETWEEN 1234 AND 4567

Great, thanks, why the *1 ?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 07:57:20

quote:
Originally posted by trouble2

quote:
Originally posted by visakh16

SELECT * FROM Table WHERE LEFT(ZipCode,4)*1 BETWEEN 1234 AND 4567

Great, thanks, why the *1 ?

For converting string to interger.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 07:57:34

quote:
Originally posted by ms65g

SELECT * FROM table WHERE SUBSTRING(zipcodes,1,4)+0 BETWEEN 1234 AND 4567

2 Issues with that. It will fail if there is a zip code where one of the first 4 characters is non-numeric, and it will ignore any indexes on zipcode. Try this:

SELECT * FROM table WHERE zipcodes BETWEEN '1234' AND '4567ZZ'

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 07:58:29

I'm thinking of sometging like this:
And
(@ZipCodeFrom = '' or (Left(C.ZipCode,4) >= @ZipCodeFrom))
And
(@ZipCodeTo = '' or (Left(C.ZipCode,4) <= @ZipCodeTo))

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 07:59:33

quote:
Originally posted by trouble2

quote:
Originally posted by visakh16

SELECT * FROM Table WHERE LEFT(ZipCode,4)*1 BETWEEN 1234 AND 4567

Great, thanks, why the *1 ?

to implicitly convert it to int else it will compare it as a string value rather than int

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 08:01:29

How about this:

(@ZipCodeFrom = '' or (Left(C.ZipCode,4) >= @ZipCodeFrom))
And
(@ZipCodeTo = '' or (Left(C.ZipCode,4) <= @ZipCodeTo))

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 08:06:24

But then I should do:

And
(@ZipCodeFrom = '' or (Left(C.ZipCode,4)*1 >= (@ZipCodeFrom)*1))
And
(@ZipCodeTo = '' or (Left(C.ZipCode,4)*1 <= (@ZipCodeTo)*1))

Or is there a better way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 08:06:51

quote:
Originally posted by trouble2

How about this:

(@ZipCodeFrom = '' or (Left(C.ZipCode,4) >= @ZipCodeFrom))
And
(@ZipCodeTo = '' or (Left(C.ZipCode,4) <= @ZipCodeTo))

The secret to creativity is knowing how to hide your sources. (Einstein)

this is fine. you can * 1 if you need to consider them as integer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andros30
Yak Posting Veteran

80 Posts

Posted - 2010-05-20 : 10:22:03

I've been searching for something similar to this but for the state. I'm trying to extract the 2 letter State from an address.

Albuquerque, NM 87114
Spokane, WA 99218

How can I pull out NM and WA from the above sample?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 10:25:57

Is this format consistent? City followed by a commaand a space and then state code?

andros30
Yak Posting Veteran

80 Posts

Posted - 2010-05-20 : 11:17:31

quote:
Originally posted by vijayisonly

Is this format consistent? City followed by a commaand a space and then state code?

The format will always be consistent as shown... I was able to figured this out while tinkering with the query and this is what I got:

substring(right(Address,11),4,2) as St

I don't know if there is a better solution.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 11:36:47

Try this too

select substring(Address,charindex(', ',Address) + 2,2)

andros30
Yak Posting Veteran

80 Posts

Posted - 2010-05-20 : 11:45:39

quote:
Originally posted by vijayisonly

Try this too
select substring(Address,charindex(', ',Address) + 2,2)

That is awesome... I like your solution better. Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-20 : 11:48:13

[code]select
*,
State = left(right(Address,8),2)
from
( -- Test Data
select Address = 'Albuquerque, NM 87114' union all
select Address = 'Spokane, WA 99218'
) a[/code]

Results:
[code]Address State
--------------------- -----
Albuquerque, NM 87114 NM
Spokane, WA 99218 WA[/code]

CODO ERGO SUM

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 11:48:58

quote:
Originally posted by andros30

quote:
Originally posted by vijayisonly

Try this too
select substring(Address,charindex(', ',Address) + 2,2)

That is awesome... I like your solution better. Thanks

Great. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources