To followup on my previous post about doing ip to numeric address conversions. It turns out that the sql functions I linked to didn’t quite work, they were written with the assumption that you were storing the data as an INT rather than a BIGINT, so for values over 127.0.0.1 it would store the number as a negative number in the database. My source data uses bigint so I got errors back when feeding in data that overflowed the int.
If you are interested I have included the edited functions after the link
drop function dbo.ipStringToInt
go
CREATE FUNCTION dbo.ipStringToInt
(
@ip CHAR(15)
)
RETURNS bigINT
AS
BEGIN
DECLARE @rv bigINT,
@o1 bigINT,
@o2 bigINT,
@o3 bigINT,
@o4 bigINT,
@base bigINT
SELECT
@o1 = CONVERT(INT, PARSENAME(@ip, 4)),
@o2 = CONVERT(INT, PARSENAME(@ip, 3)),
@o3 = CONVERT(INT, PARSENAME(@ip, 2)),
@o4 = CONVERT(INT, PARSENAME(@ip, 1))
IF (@o1 BETWEEN 0 AND 255)
AND (@o2 BETWEEN 0 AND 255)
AND (@o3 BETWEEN 0 AND 255)
AND (@o4 BETWEEN 0 AND 255)
BEGIN
SET @rv = (@o1 * 16777216)+
(@o2 * 65536) +
(@o3 * 256) +
(@o4)
END
ELSE
SET @rv = -1
RETURN @rv
END
go
drop function dbo.ipIntToString
go
CREATE FUNCTION dbo.ipIntToString
(
@ip bigINT
)
RETURNS CHAR(15)
AS
BEGIN
DECLARE @o1 bigINT,
@o2 bigINT,
@o3 bigINT,
@o4 bigINT
IF ABS(@ip) > 4294967295
RETURN '255.255.255.255'
SET @o1 = @ip / 16777216
IF @o1 = 0
SELECT @o1 = 255, @ip = @ip + 16777216
ELSE IF @o1 < 0
BEGIN
IF @ip % 16777216 = 0
SET @o1 = @o1 + 256
ELSE
BEGIN
SET @o1 = @o1 + 255
IF @o1 = 128
SET @ip = @ip + 2147483648
ELSE
SET @ip = @ip + (16777216 * (256 - @o1))
END
END
ELSE
BEGIN
SET @ip = @ip - (16777216 * @o1)
END
SET @ip = @ip % 16777216
SET @o2 = @ip / 65536
SET @ip = @ip % 65536
SET @o3 = @ip / 256
SET @ip = @ip % 256
SET @o4 = @ip
RETURN
CONVERT(VARCHAR(4), @o1) + '.' +
CONVERT(VARCHAR(4), @o2) + '.' +
CONVERT(VARCHAR(4), @o3) + '.' +
CONVERT(VARCHAR(4), @o4)
END