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.<\/p>\n
create FUNCTION [dbo].[ufn_IPAddressToTable]
\n(<\/p>\n@IpAddress VARCHAR(15)<\/p>\n
)
\nRETURNS @IpTable TABLE (part1 int, part2 int, part3 int, part4 int)
\nAS
\nBEGIN<\/p>\nDECLARE @part1 int, @part2 int, @part3 int, @part4 int
\nIF (not @IpAddress is null)
\nBEGIN<\/p>\nif (CHARINDEX(‘.’, @IpAddress) > 0)
\nbegin<\/p>\nset @part1 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
\nset @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)<\/p>\nif (CHARINDEX(‘.’, @IpAddress) > 0)
\nbegin<\/p>\nset @part2 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
\nset @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)<\/p>\nif (CHARINDEX(‘.’, @IpAddress) > 0)
\nbegin<\/p>\nset @part3 = CONVERT(int, substring(@IpAddress, 0, CHARINDEX(‘.’, @IpAddress)))
\nset @IpAddress = substring(@IpAddress, CHARINDEX(‘.’, @IpAddress) + 1, 100)
\nset @part4 = CONVERT(int, @IpAddress)<\/p>\nend<\/p>\n
end<\/p>\n
end
\nif (not(@part1 is null or @part2 is null or @part3 is null or @part4 is null))<\/p>\ninsert @IpTable(part1, part2, part3, part4)
\nvalues (@part1, @part2, @part3, @part4)<\/p>\nEND
\nRETURN<\/p>\nEND<\/p><\/blockquote>\n
As an example you can use it like this:<\/p>\n
declare @IpAddress varchar(15)
\nset @IpAddress = ‘127.0.0.1’
\nselect * from dbo.ufn_IPAddressToTable(@IpAddress)<\/p><\/blockquote>\nOr if you have a table with ip addresses<\/p>\n
with ComputerIps(Area, IpPart1, IpPart2, IpPart3)
\nas
\n(<\/p>\nselect\u00a0 c.Area,
\n(select top 1 part1 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part1,
\n(select top 1 part2 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part2,
\n(select top 1 part3 from dbo.[ufn_IPAddressToTable](c.IpAddress)) as Part3
\nfrom Computers c
\nwhere not (c.IpAddress is null)\u00a0 and LEN(c.IpAddress) > 0<\/p>\n)
\nselect Area, IpPart1, IpPart2, IpPart3, COUNT(*) as [Computers]
\nfrom ComputerIps
\ngroup by Area, IpPart1, IpPart2, IpPart3
\norder by Area, IpPart1, IpPart2, IpPart3<\/p><\/blockquote>\nAnother problem is that it may be slow when the source table becomes large. At least it helps a bit.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n