Converting IP Addresses to Integers and Back

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)

Converting IP Addresses to Integers and Back

Author

Topic

sp_wiz
Yak Posting Veteran

55 Posts

Posted - 2002-11-14 : 04:42:53

Does any of you bight sparks have a script that will do this for me

Cheers

Robp

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-14 : 06:22:54

read the following topic.....(found via a forum search using keywords "IP Address Convert" (surprise!!!))

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19276

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-11-14 : 09:36:08

Robp, I manage a large Network Monitoring database and so I had to tackle this same issue. I convert the IP's to char(12) and then do my lookup.

First off, kudos to RobVolk for the PARSENAME function (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6618).


use TempDb
go

set nocount on

create table #IpRange (Division varchar(15) , Start_IP varchar(15) , End_IP varchar(15) )

insert #IpRange select 'North' , '175.25.16.0' , '175.25.17.255'
insert #IpRange select 'North' , '175.25.18.0' , '175.25.34.255'

insert #IpRange select 'South' , '2.24.0.0' , '2.24.0.3'
insert #IpRange select 'South' , '2.24.0.4' , '2.24.0.7'
insert #IpRange select 'South' , '2.24.52.0' , '2.24.53.255'

insert #IpRange select 'East' , '216.25.112.0' , '216.25.119.255'
insert #IpRange select 'East' , '216.25.120.0' , '216.25.255.255'
go


CREATE FUNCTION ufn_PadIp (@IP varchar(15))  
RETURNS char(12)
 AS  
BEGIN 

declare @paddedIP char(12)
select @paddedIP = ''

If charIndex('.',@IP) > 0
	SELECT @paddedIP = CONVERT(char(12), 
				REPLICATE('0', 3 - LEN(PARSENAME(@IP, 4))) + PARSENAME(@IP, 4) + 
				REPLICATE('0', 3 - LEN(PARSENAME(@IP, 3))) + PARSENAME(@IP, 3) + 
				REPLICATE('0', 3 - LEN(PARSENAME(@IP, 2))) + PARSENAME(@IP, 2) + 
				REPLICATE('0', 3 - LEN(PARSENAME(@IP, 1))) + PARSENAME(@IP, 1)
			        )

Return(@paddedIP)

END
go

print '** Show all records with padded start and end IPs **'

select Division, Start_IP , End_IP
, TempDb.dbo.ufn_PadIP(Start_IP) as Padded_Start_IP 
, TempDb.dbo.ufn_PadIP(End_IP) as Padded_End_IP
from #IpRange
order by 4, 5


declare @ip varchar(15) , @msg varchar(200)

select @ip = '2.24.52.126'


select @msg = '** Get the data for IP address ' + @ip + ' **'
print ''
print @msg

select * from #IpRange
where TempDb.dbo.ufn_PadIP(@ip) 
	between TempDb.dbo.ufn_PadIP(Start_IP) and TempDb.dbo.ufn_PadIP(End_IP) 


drop table #IpRange
drop function ufn_PadIP

/*** Here are the results ***/

** Show all records with padded start and end IPs **
Division        Start_IP        End_IP          Padded_Start_IP Padded_End_IP 
--------------- --------------- --------------- --------------- ------------- 
South           2.24.0.0        2.24.0.3        002024000000    002024000003
South           2.24.0.4        2.24.0.7        002024000004    002024000007
South           2.24.52.0       2.24.53.255     002024052000    002024053255
North           175.25.16.0     175.25.17.255   175025016000    175025017255
North           175.25.18.0     175.25.34.255   175025018000    175025034255
East            216.25.112.0    216.25.119.255  216025112000    216025119255
East            216.25.120.0    216.25.255.255  216025120000    216025255255

 
** Get the data for IP address 2.24.52.126 **
Division        Start_IP        End_IP          
--------------- --------------- --------------- 
South           2.24.52.0       2.24.53.255

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources