Skip to content
Create a gist now

Instantly share code, notes, and snippets.

SQL: Converting an IPV4 or IPV6 To An Integer And Back Again
/* CREATE UTILITY TABLE WITH NUMBERS - USED BY EXTRACT FUNCTION */
IF OBJECT_ID('dbo.Numbers') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers(n INT PRIMARY KEY)
INSERT INTO dbo.Numbers
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 1 AND 100
GO
/* CREATE EXTRACT FUNCTION - USED TO GET PARTS OF IP ADDRESS */
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Extract' AND xtype IN ('FN', 'IF', 'TF'))
DROP FUNCTION dbo.Extract
GO
CREATE FUNCTION dbo.Extract(@string VARCHAR(200), @delimiter CHAR(1), @idx INT)
RETURNS VARCHAR(200)
AS
BEGIN
RETURN
CASE @idx
WHEN 0 THEN @string
ELSE
(
SELECT string
FROM
(
SELECT SUBSTRING(@string, n, CHARINDEX( @delimiter, @string + @delimiter, n ) - n ), n + 1 - LEN(REPLACE(LEFT(@string, n), @delimiter, ''))
FROM Numbers
WHERE SUBSTRING(@delimiter + @string, n, 1) = @delimiter
AND n < LEN(@string) + 1) AS T(string, idx)
WHERE idx = @idx
)
END
END
GO
/* CREATE IPADDRESSTOINTEGER FUNCTION */
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'IPAddressToInteger' AND xtype IN ('FN', 'IF', 'TF'))
DROP FUNCTION dbo.IPAddressToInteger
GO
CREATE FUNCTION dbo.IPAddressToInteger (@IP AS varchar(23), @VERSION AS varchar(4))
RETURNS bigint
AS
BEGIN
DECLARE @Result BIGINT
/* IP address is IPv4 */
IF @VERSION = 'IPV4'
SET @Result =
CONVERT(bigint, dbo.Extract(@IP, '.', 4)) +
CONVERT(bigint, dbo.Extract(@IP, '.', 3)) * 256 +
CONVERT(bigint, dbo.Extract(@IP, '.', 2)) * 65536 +
CONVERT(bigint, dbo.Extract(@IP, '.', 1)) * 16777216
/* IP address is IPv6 */
ELSE
SET @Result =
CONVERT(bigint, dbo.Extract(@IP, '.', 6)) +
CONVERT(bigint, dbo.Extract(@IP, '.', 5)) * 256 +
CONVERT(bigint, dbo.Extract(@IP, '.', 4)) * 65536 +
CONVERT(bigint, dbo.Extract(@IP, '.', 3)) * 16777216 +
CONVERT(bigint, dbo.Extract(@IP, '.', 2)) * 4294967296 +
CONVERT(bigint, dbo.Extract(@IP, '.', 1)) * 1099511627776
RETURN (@Result)
END
GO
/* CREATE INTEGERTOIPADDRESS FUNCTION */
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'IntegerToIPAddress' AND xtype IN ('FN', 'IF', 'TF'))
DROP FUNCTION dbo.IntegerToIPAddress
GO
CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS bigint, @VERSION AS varchar(4))
RETURNS varchar(23)
AS
BEGIN
DECLARE @Result varchar(23)
DECLARE @Octet1 bigint
DECLARE @Octet2 bigint
DECLARE @Octet3 bigint
DECLARE @Octet4 bigint
DECLARE @Octet5 bigint
DECLARE @Octet6 bigint
DECLARE @RestOfIP bigint
SET @Octet1 = @IP / 1099511627776
SET @RestOfIP = @IP - (@Octet1 * 1099511627776)
SET @Octet2 = @RestOfIP / 4294967296
SET @RestOfIP = @RestOfIP - (@Octet2 * 4294967296)
SET @Octet3 = @RestOfIP / 16777216
SET @RestOfIP = @RestOfIP - (@Octet3 * 16777216)
SET @Octet4 = @RestOfIP / 65536
SET @RestOfIP = @RestOfIP - (@Octet4 * 65536)
SET @Octet5 = @RestOfIP / 256
SET @Octet6 = @RestOfIP - (@Octet5 * 256)
/* IP address is IPv4 */
IF @VERSION = 'IPV4'
SET @Result = (
CONVERT(varchar, @Octet3) + '.' +
CONVERT(varchar, @Octet4) + '.' +
CONVERT(varchar, @Octet5) + '.' +
CONVERT(varchar, @Octet6)
)
/* IP address is IPv6 */
ELSE
SET @Result = (
CONVERT(varchar, @Octet1) + '.' +
CONVERT(varchar, @Octet2) + '.' +
CONVERT(varchar, @Octet3) + '.' +
CONVERT(varchar, @Octet4) + '.' +
CONVERT(varchar, @Octet5) + '.' +
CONVERT(varchar, @Octet6)
)
RETURN (@Result)
END
GO
/* LET'S RUN SOME TESTS */
/* ipv4 conversion */
/* returns 1181772947 */
SELECT dbo.IPAddressToInteger('70.112.108.147','IPV4');
/* returns 70.112.108.147 */
SELECT dbo.IntegerToIPAddress('1181772947','IPV4');
/* ipv6 conversion */
/* returns 77448671886203 */
SELECT dbo.IPAddressToInteger('70.112.108.147.123.123','IPV6');
/* returns 70.112.108.147.123.123 */
SELECT dbo.IntegerToIPAddress('77448671886203','IPV6');
@chrislim2888

Can you enhance the script to work with the IPv6 with the follow syntax, 2001:db8::1428:57ab?

@nhbinh
nhbinh commented Feb 15, 2016

this is useless, waste of time

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.