IN and range-values - 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 2000 Forums

SQL Server Development (2000)

IN and range-values

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 14:22:02

Yaks;

I need to do a search in my db based on zip-codes and states. The State "Akershus" (which is in Norway) has int-value zipcodes and they are in the following ranges: 1300-1488, 1540-1556, 1900-1941, 1954-2093, 2150-2170

I was hoping to do a WHERE like this:

...WHERE Zip IN (1300-1488, 1540-1556, 1900-1941, 1954-2093, 2150-2170)

but I had no such luck. I'm quite confident that I'm not inventing the wheel here (if so please let me know

) so I would really like some insights if you guys have any...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-02-15 : 14:25:45

Is this a single column holding the range or is there a column for the start of the range and a column for the end of the range. If there are 2 columns you could use a BETWEEN clause.


SELECT ...
FROM ...
WHERE EXISTS(SELECT * FROM <ZipCodeTable> WHERE Zip BETWEEN <ZipCodeTable>.Start AND <ZipCodeTable>.End)

Dustin Michaels

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-15 : 14:38:44

[code]
WHERE Zip BETWEEN 1300 AND 1488
OR Zip BETWEEN 1540 AND 1556
OR Zip BETWEEN 1900 AND 1941
OR Zip BETWEEN 1954 AND 2093
OR Zip BETWEEN 2150 AND 2170

[/code]

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 14:41:50

I have a customers-table with name, adress, zip-code and so on and I would like to list all customers in the state "Akershus". Company A has zip-code 1365 and Company B has zip-code 1550 and I would like to hit both with my pseudo WHERE/IN-clause above. Making my nice little WHERE/IN-statement in to 6 nasty BETWEEN-statements is not preferable as there are quite a few ranges with the other states and it could get messy...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 14:49:29

So State is not a column in your customers table? If not:

The "between" solution wouldn't be too good with multiple zip ranges. Because you would have to say:
Where (zip between 1 and 3 OR zip between 7 and 10).
The OR would force a table scan.

I think a better solution would be to have a StateZipcode table. One record for each State/Zip combination. Instead of: "where zip in(..."
you would join on Zip and say: where State='Akershus'

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 15:02:19

Well, I can't say that I'm completely happy with either of the solutions...your's TG, allthough it will mean more work for me, is probably the one I'll be using if no other ingenious contributions are made. So much for simple dream-solutions...tough luck I guess...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 15:22:09

try this. i used northwind.


use northwind
CREATE FUNCTION dbo.ZipStuff
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	ZipFrom int,
	ZipTo int
) 
AS  
BEGIN 
declare @temp nvarchar(2000)
	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		set @temp = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
		Insert Into @RtnValue (ZipFrom, ZipTo)
		Select parsename( replace (@temp, '-', '.'), 2), parsename( replace (@temp, '-', '.'), 1)
		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
	End

	set @temp = ltrim(rtrim(@RowData))
	Insert Into @RtnValue (ZipFrom, ZipTo)
	Select parsename( replace (@temp, '-', '.'), 2), parsename( replace (@temp, '-', '.'), 1)

	Return
END

go

declare @zips nvarchar(1000)
set @zips = '10250-10260,10280-10290,10800-10850,11000-11010'

select 	*
from	orders t1 
	cross join dbo.ZipStuff(@zips, ',') t2 
where	t1.orderid between t2.zipfrom and t2.zipto

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 15:28:35

Another possibility would be to create a UserDefinedFunction that returns a table. (one row for each zip)

It would still take work to code the function but you wouldn't need to change/add database tables. You'd use it in one of 2 ways:

JOIN   dbo.fnParseMultiRangeToTable('1300~1488,1540~1556,1900~1941,1954~2093,2150~2170') as zips
	   ON zips.zip = customers.zip

-OR-

where zip in (
Select zip from dbo.fnParseMultiRangeToTable('1300~1488,1540~1556,1900~1941,1954~2093,2150~2170')
)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 15:29:28

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 16:49:37

Now THAT my Slovenian friend earns you the right to unlimited free accomodation on my couch in Oslo! That is way cool

I totally appreciate your help TG/Dustin/Chad but I think we should all agree that Spirits solution is the best (easiest for me that is). Thanx a bunch!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 17:41:11

well when i visit Oslo, i'll hold you to that

Go with the flow & have fun! Else fight the flow

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 17:45:42

As long as most of the family stays home that's fine, my couch isn't that big

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 17:52:51

ROTFL!!
what family??

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 17:58:51

Well I assume you're taking me with you!!

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 18:01:18

Hey, as long as Brett throws the marguaritas I'll have you all!! But it might be somewhat crowded on the couch...but after a handful of marguaritas who cares *burp*

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 18:15:09

sure TG no problem. We'll have Lumbago introduce us to 2 of his cuter single
female friends and we can have a blast...especialy with those margharitas provided by brett

Go with the flow & have fun! Else fight the flow

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-02-15 : 18:19:15

Hehe, we can do a swap...you bring the lovely slovenian honeys with a few american chicas on the side, and I'll throw in some cheeky blonde scandinavians for'ya. Sounds like a plan! Hehe...grrrr

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources