Quick Search
»

# IP Address Conversion Between Integer and String - SQL Server

Modified on Tue, Dec 29, 2015, 11:28 AM 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-2019, Patrick Jasinski.