Rudolf blog of all

I have a requirement to do some stuff with a table that contains ip addresses like grouping per subnets etc. Doing this with straight tsql is tricky (if possible at all) so I created a sql server function that takes the ip address field and breaks it down to a set (table) of 4 values. It’s not perfect and brings another type of complexity of its own but at least you can compare the separate bits of the ip address as unique parts. Also, it doesn’t really have any error checking built in.

create FUNCTION [dbo].[ufn_IPAddressToTable]
(

@IpAddress VARCHAR(15)

)
RETURNS @IpTable TABLE (part1 int, part2 int, part3 int, part4 int)
AS
BEGIN

DECLARE @part1 int, @part2 int, @part3 int, @part4 int
IF (not @IpAddress is null)
BEGIN

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part1 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part2 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)

if (CHARINDEX(‘.’, @IpAddress) > 0)
begin

set @part3 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
set @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)
set @part4 = CONVERT(int, @IpAddress)

end

end

end
if (not(@part1 is null or @part2 is null or @part3 is null or @part4 is null))

insert @IpTable(part1, part2, part3, part4)
values (@part1, @part2, @part3, @part4)

END
RETURN

END

As an example you can use it like this:

declare @IpAddress varchar(15)
set @IpAddress = ‘127.0.0.1’
select * from dbo.ufn_IPAddressToTable(@IpAddress)

Or if you have a table with ip addresses

with ComputerIps(Area, IpPart1, IpPart2, IpPart3)
as
(

select  c.Area,
(select top 1 part1 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part1,
(select top 1 part2 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part2,
(select top 1 part3 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part3
from Computers c
where not (c.IpAddress is null)  and LEN(c.IpAddress) > 0

)
select Area, IpPart1, IpPart2, IpPart3, COUNT(*) as [Computers]
from ComputerIps
group by Area, IpPart1, IpPart2, IpPart3
order by Area, IpPart1, IpPart2, IpPart3

Another problem is that it may be slow when the source table becomes large. At least it helps a bit.