Converting IP addresses from decimal to dotted decimal notation and back

From: BT1002 (btnospm_at_hotmail.com)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 09:56:06 -0800

There are applications that store IP addresses in decimal form for various reasons ...
I wasn't able to find SQL functions for performing the conversion, so I went ahead and wrote them. Just thought I would share the code in case anyone else needed it.

--Creating a function to convert from a decimal IP address to dotted decimal notation

create function convert_dec_ip(@Incoming bigint)
returns varchar(20)
as
Begin
Declare @result1 varchar(20)

Declare @octet1 bigint
Declare @octet2 bigint
Declare @octet3 bigint
Declare @octet4 bigint

select @octet1 = @Incoming / 16777216
select @octet2 = (@Incoming - (@octet1*16777216))/65536
select @octet3 = (@Incoming - ((@octet1*16777216)+(@octet2*65536)))/256
select @octet4 = @Incoming-((@octet1*16777216)+(@octet2*65536)+(@octet3*256))

select @result1 = cast(@octet1 as varchar(3)) + '.' + cast(@octet2 as varchar(3)) + '.' + cast(@octet3 as varchar(3)) + '.' + cast(@octet4 as varchar(3))

return @result1
End

GO

--Creating a function to convert from dotted decimal notation to decimal IP address form.

create function convert_ip_dec(@Incoming varchar(20))
returns bigint
as
Begin
Declare @result1 bigint

Declare @octet1 bigint
Declare @octet2 bigint
Declare @octet3 bigint
Declare @octet4 bigint
Declare @ci1 int
Declare @ci2 int
Declare @ci3 int
Declare @ci4 int

select @ci1 = cast((charindex('.', @Incoming)) as bigint)
select @octet1 = cast((substring(@Incoming, 0, @ci1)) as bigint)

select @ci2 = cast((charindex('.', @Incoming, @ci1+1)) as bigint)
select @octet2 = cast((substring(@Incoming, @ci1+1, (@ci2-@ci1-1))) as bigint)

select @ci3 = cast((charindex('.', @Incoming, @ci2+1)) as bigint)
select @octet3 = cast((substring(@Incoming, @ci2+1, (@ci3-@ci2-1))) as bigint)

select @ci4 = len(@Incoming) - @ci3
select @octet4 = cast((right(@Incoming, @ci4)) as bigint)

select @result1 = (@octet1 * 16777216) + (@octet2 * 65536) + (@octet3 * 256) + @octet4

return @result1
End
go

Relevant Pages