Between statement

[Kuriyama]

I have a table that has a list of zip codes, and data relating to these zip codes. I need to pull data out of this table based on an incoming zip code. The problem is that the column I need to compare the incoming zip code to is not always a single value. For instance. . .

Incoming zip = 55555
Row with info = 50000-60000

I need a way to pull that row out of SQL.

I'm trying to use a combination of LEFT, RIGHT, and BETWEEN but I'm failing.

Here is my idea.

select * from zips where zip = '55555' OR between left(zip, 5) AND right(zip, 5)

Any ideas?

[yamaharuss]

So you're trying to compare a numeric value to a string in a query? You may want to rethink the data structure of your zipcodes table.

Are you trying to compare distances by any chance? I have a pretty good table and ASP schema for calculating distances by zip code.

[Nedals]

Try putting your 'between' section in parens. The OR and AND may be confusing the SQL processor
select * from zips where zip = '55555' OR (zip between left(zip, 5) AND right(zip, 5))
If that still does not work, add quotes so the the extracted zips are treated as strings
select * from zips where zip = '55555' OR (zip between 'left(zip, 5)' AND 'right(zip, 5)')

[chazzy]

The problem is that you're using bad syntax. Use the first example in Nedals' post above, it should work, even if the type is a varchar.

[Kuriyama]

So you're trying to compare a numeric value to a string in a query? You may want to rethink the data structure of your zipcodes table.

Are you trying to compare distances by any chance? I have a pretty good table and ASP schema for calculating distances by zip code.

Na attempting to pull different rates out of other tables. If any of you guys have seen the UPS shipping rate tables, that is what I'm working with.


I tried use Nedal's query's but that didn't work. Always returns every row, and the second one returns nothing. I'm using SQL2000 if that helps.

[Kuriyama]

hehe I did something stupid here. Nedal's query worked. Thanks for helping me guys, I'm a sql newbie :)

[Technoroj]

Try putting your 'between' section in parens. The OR and AND may be confusing the SQL processor

Code:
select * from zips where zip = '55555' OR (zip between left(zip, 5) AND right(zip, 5))If that still does not work, add quotes so the the extracted zips are treated as strings

Code:
select * from zips where zip = '55555' OR (zip between 'left(zip, 5)' AND 'right(zip, 5)')

[Nedals]

Technoroj...
Any Particular reason you posted that. :(