Search IP Ranges and exclude

Question

Search IP Ranges and exclude

TJT

SSCommitted

Points: 1926
More actions
June 1, 2015 at 7:23 am

#398529

I need to remove some IP addresses from a total list of IP addresses. The ones I need to remove are in certain IP ranges. I could create a temp table with all these IP addresses I need to exclude, but I was wondering if there was a better way to do this?
Example IP Ranges:
128.134.2.100 to 128.134.2.200
135.234.12.60 to 135.234.12.120
210.10.140.150 to 210.10.140.200

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

IPV4 addresses are represented as strings in the dotted format, but they're integers (bigints precisely).

You can easily convert to bigint using the function provided by Denis Gobo here: http://sqlblog.com/blogs/denis_gobo/archive/2008/10/05/9266.aspx

CREATE FUNCTION dbo.IPAddressToInteger (@IP AS varchar(15))

RETURNS bigint

AS

BEGIN

RETURN (CONVERT(bigint, PARSENAME(@IP,1)) +

CONVERT(bigint, PARSENAME(@IP,2)) * 256 +

CONVERT(bigint, PARSENAME(@IP,3)) * 65536 +

CONVERT(bigint, PARSENAME(@IP,4)) * 16777216)

END

Using the conversion function will likely be slow (any predicate will not be SARGable) but will let you use the ranges, which you won't be able to use with the string representation.

Another option could be to precalculate the bigint version of the address in a computed column and index it.

-- Gianluca Sartori

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

I'm not sure what you mean by "removing" addresses from a "list". However, the following example assumes your need is to delete rows from a table where IPAddress is contained within three different IPAddress ranges.

delete from Machines

where (IPAddress between '128.134.2.100' and '128.134.2.200')

or (IPAddress between '135.234.12.60' and '135.234.12.120')

or (IPAddress between '210.10.140.150' and '210.10.140.200');

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TJT SSCommitted Points: 1926 More actions · Answer 3

TJT

SSCommitted

Points: 1926

June 1, 2015 at 7:53 am

#1801985

Thanks this works

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Be careful: won't work for addresses represented with leading zeros, such as 128.134.002.101

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Here is some code that I hope will help you:

IF OBJECT_ID('IPAddressToInteger') IS NOT NULL

DROP FUNCTION IPAddressToInteger;

GO

-- Deterministic version of Denis Gobo's function

-- Parsename is not deterministic

CREATE FUNCTION [dbo].IPAddressToInteger( @IPV4Address VarChar(15))

RETURNS BIGINT

WITH SCHEMABINDING -- Deterministic function.

BEGIN

DECLARE @Dot1 AS int = CHARINDEX( '.', @IPV4Address );

DECLARE @Dot2 AS int = CHARINDEX( '.', @IPV4Address, @Dot1 + 1 );

DECLARE @Dot3 AS int = CHARINDEX( '.', @IPV4Address, @Dot2 + 1 );

RETURN CAST( SUBSTRING( @IPV4Address, 0, @Dot1 ) AS bigint ) * 0x1000000 +

CAST( SUBSTRING( @IPV4Address, @Dot1 + 1, @Dot2 - @Dot1 - 1 ) AS bigint ) * 0x10000 +

CAST( SUBSTRING( @IPV4Address, @Dot2 + 1, @Dot3 - @Dot2 - 1 ) AS bigint ) * 0x100 +

CAST( SUBSTRING( @IPV4Address, @Dot3 + 1, LEN( @IPV4Address ) * 1 ) AS bigint );

END

GO

IF OBJECT_ID('tempdb..#addresses') IS NOT NULL

DROP TABLE #addresses;

GO

-- Sample table

CREATE TABLE #addresses (

IP_as_char varchar(15) PRIMARY KEY,

IP_as_bigint AS

-- Horribly verbose version of the above conversion function

-- Write once, forget forever

CAST( SUBSTRING( IP_as_char, 0, CHARINDEX( '.', IP_as_char ) ) AS bigint ) * 0x1000000 +

CAST( SUBSTRING( IP_as_char, CHARINDEX( '.', IP_as_char ) + 1, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char ) + 1 ) - CHARINDEX( '.', IP_as_char ) - 1 ) AS bigint ) * 0x10000 +

CAST( SUBSTRING( IP_as_char, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char ) + 1 ) + 1, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char ) + 1 ) + 1 ) - CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char ) + 1 ) - 1 ) AS bigint ) * 0x100 +

CAST( SUBSTRING( IP_as_char, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char, CHARINDEX( '.', IP_as_char ) + 1 ) + 1 ) + 1, LEN( IP_as_char ) * 1 ) AS bigint )

);

CREATE INDEX IX_addresses_bigint ON #addresses(IP_as_bigint);

GO

-- Insert some addresses without leading zeros

INSERT INTO #addresses (IP_as_char)

VALUES('128.134.2.100'),('128.134.2.101'),('128.134.2.102'),('128.134.2.230');

GO

-- Insert some addresses with leading zeros

INSERT INTO #addresses (IP_as_char)

VALUES('128.134.002.100'),('128.134.002.101'),('128.134.002.102'),('128.134.002.230');

GO

-- Interprets addresses correctly

SELECT *

FROM #addresses a

WHERE IP_as_bigint NOT BETWEEN dbo.IPAddressToInteger('128.134.2.100') AND dbo.IPAddressToInteger('128.134.2.200');

-- Uh-oh! Wrong results...

SELECT *

FROM #addresses a

WHERE IP_as_char NOT BETWEEN '128.134.2.100' AND '128.134.2.200'

-- Gianluca Sartori

TJT SSCommitted Points: 1926 More actions · Answer 6

🙁

Thanks Gianluca

So when the command prompt returns 128.134.2.100 - it is really 128.134.002.100 ??

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

It's a number, so they're the same thing. Depending on where the IP addresses come from, they can be in any form.

-- Gianluca Sartori