IP Filtering - 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

Transact-SQL (2000)

IP Filtering

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-08 : 04:03:27

If we are storing IP in a table then how can we exlude an IP range from the resultset. Just like this

select ip from @table
where ip < '68.142.192.0' and ip > '68.142.255.255'

or by some other way

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 04:11:18

I think replacing all Dots to empty will work

select ip from @table where
replace(ip,'.','') between replace('68.142.192.0','.','') and replace('68.142.255.255','.','')

But I am not sure whether this is efficient way

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-08 : 04:18:03

just look out for those single zeroes

quote:
Originally posted by madhivanan

I think replacing all Dots to empty will work

select ip from @table where
replace(ip,'.','') between replace('68.142.192.0','.','') and replace('68.142.255.255','.','')

But I am not sure whether this is efficient way

Madhivanan

Failing to plan is Planning to fail

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 04:40:16

>>just look out for those single zeroes

Jen, I didnt understand what you said
Can you explain it?

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-09-08 : 04:47:12

Thanks Madhivanan

I was thinking about the same technique but at the same time I was looking for some better solution.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-08 : 05:43:41

removing the periods may not work all by itself because each part of the ip addres is made up of 3 numbers
look at this example: 68.142.192.12 and 68.142.192.0

so if you have 6 814 219 212 and you try to compare with 681 421 920

look at the wide range? it's supposed to be a range from the same subnet from 0 to 12 only not from several thousands to a million

so to compensate you should have

68 142 192 012 and 68 142 192 000

quote:
Originally posted by madhivanan

>>just look out for those single zeroes

Jen, I didnt understand what you said
Can you explain it?

Madhivanan

Failing to plan is Planning to fail

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 05:51:48

Well. I didnt aware on that

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-08 : 06:08:52

it's always wonderful to learn something new everyday noh?

this is why this forum is great, professionals from different areas of IT contributing to help each other...

quote:
Originally posted by madhivanan

Well. I didnt aware on that

Madhivanan

Failing to plan is Planning to fail

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 06:13:58

Yes it is.
I am a learner and not DBA or expert like you

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-08 : 08:17:51

Some sample code to work with


Declare @table table (ip varchar(15))
Insert Into @table Select '68.142.201.3'
Insert Into @table Select '68.142.192.0'
Insert Into @table Select '68.143.200.157'
Insert Into @table Select '132.189.214.0'
Insert Into @table Select '25.189.214.0'

--Maddy's shot
select ip from @table where 
replace(ip,'.','') between replace('68.142.192.0','.','') and replace('68.142.255.255','.','')

--Counter example
select ip from @table where 
replace(ip,'.','') between replace('100.142.192.0','.','') and replace('255.142.255.255','.','')


Declare @ip_Begin varchar(15),
	@ip_End varchar(15) 

Set @ip_Begin = '100.142.192.0'
Set @ip_End = '255.142.192.0'

/*
Set @ip_Begin = '68.142.192.0'
Set @ip_End = '68.142.255.255'
*/


Select * 
From @table
Where 
	right('000'+parsename(ip,4),3) + right('000'+parsename(ip,3),3) + right('000'+parsename(ip,2),3) + right('000'+parsename(ip,1),3)
Between
	right('000'+parsename(@ip_Begin,4),3) + right('000'+parsename(@ip_Begin,3),3) + right('000'+parsename(@ip_Begin,2),3) + right('000'+parsename(@ip_Begin,1),3)
	And
	right('000'+parsename(@ip_End,4),3) + right('000'+parsename(@ip_End,3),3) + right('000'+parsename(@ip_End,2),3) + right('000'+parsename(@ip_End,1),3)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 08:29:29

Well Corey. Nice display

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources