Skip to content

@simonbingham /gist:5000258
Last active

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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?

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.