Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

IP Address Conversion Between Integer and String - SQL Server

RSS
Modified on Tue, Dec 29, 2015, 11:28 AM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.