IP Address Conversion Between Integer and String - SQL Server

Overview¶

This article presents two functions to convert an IP address from a string to a BIGINT and back again.

Code¶

/* Adapted from http://bitmugger.blogspot.com/2011/02/how-to-convert-cidr-ip-address-to-range.html */
create function [dbo].[ConvertIPToLong](@IP varchar(15))
returns bigint
as
begin
    declare @Long bigint
    set @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
        CONVERT(bigint, PARSENAME(@IP, 1))

    return (@Long)
end

create function dbo.ConvertLongToIp(@IpLong bigint) 
returns varchar(15) as begin
/*--- Begin Testing ---*/
/*
declare 
    @IpLong bigint = 4294967295
*/
/*--- End Testing ---*/
declare
     @IpHex     varchar(8)
    ,@IpDotted  varchar(15)

select
     @IpHex = substring(convert(varchar(30), master.dbo.fn_varbintohexstr(@IpLong)), 11, 8)

select
    @IpDotted = convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 1, 2), 2)))) + '.' +
                convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 3, 2), 2)))) + '.' +
                convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 5, 2), 2)))) + '.' +
                convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 7, 2), 2))))


return @IpDotted
end