SQL Server 2008 R2: Check if IP address is in a range

[aconybeare]

Hi,

I'm trying to write a SP to check if a passed in IP address is in a range e.g. equal to or between a start and an end address. e.g. -

Is 10.10.10.5 equal to or between 10.10.10.1 and 10.10.10.10

Regards Al

[smendoza]

I would typically have my front end do the checking for me.

[aconybeare]

Hi,

Think I've got it whipped thanks, converting the IP address into a number (BigInt) -

I've not done any speed tests on these two methods -

This one uses PARSENAME to access the octets

http://www.firstbigstep.net/content/...umber_SQL.aspx

And this one uses a UDF split function to access the octets -

http://www.sqlservercentral.com/scri...laneous/30943/

At the moment I'm using the second one. I've also tweaked this second one to just validate the passed in IP address I.E. check that it has 4 octets and the numbers are between 0-255 returns a Boolean, I've called it IsValidIP (@IPAddress)

If anyone has any thoughts on the efficiencies of these two approaches I'd be glad to hear them.

Hopefully this helps someone in the future.

Cheers Al

PS - Currently our platform only supports V4 IP addresses