public
Last active

SQL: Converting an IPV4 or IPV6 To An Integer And Back Again

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
/* 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');

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.