Created
December 14, 2017 11:38
-
-
Save anonymous/007b8612e51f5f3ed6bb4132b4001542 to your computer and use it in GitHub Desktop.
Conversion of varchar IPv4 & IPv6 representations in binary
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2') | |
-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2') | |
-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus') | |
ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary | |
( | |
@ipAddress VARCHAR(39) | |
) | |
RETURNS BINARY(16) AS | |
BEGIN | |
DECLARE | |
@bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2) | |
, @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT | |
, @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4) | |
SELECT | |
@delim = '.' | |
, @prevColIndex = 0 | |
, @limit = 4 | |
, @vbytes = 0x | |
, @parts = 0 | |
, @colIndex = CHARINDEX(@delim, @ipAddress) | |
IF @colIndex = 0 | |
BEGIN | |
SELECT | |
@delim = ':' | |
, @limit = 8 | |
, @colIndex = CHARINDEX(@delim, @ipAddress) | |
WHILE @colIndex > 0 | |
SELECT | |
@parts = @parts + 1 | |
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) | |
SET @colIndex = CHARINDEX(@delim, @ipAddress) | |
IF @colIndex = 0 | |
RETURN NULL | |
END | |
SET @ipAddress = @ipAddress + @delim | |
WHILE @colIndex > 0 | |
BEGIN | |
SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1) | |
IF @delim = ':' | |
BEGIN | |
SET @zone = RIGHT('0000' + @token, 4) | |
SELECT | |
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)') | |
, @vbytes = @vbytes + @vbzone | |
IF @token = '' | |
WHILE @parts + 1 < @limit | |
SELECT | |
@vbytes = @vbytes + @vbzone | |
, @parts = @parts + 1 | |
END | |
ELSE | |
BEGIN | |
SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2) | |
SELECT | |
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)') | |
, @vbytes = @vbytes + @vbzone | |
END | |
SELECT | |
@prevColIndex = @colIndex | |
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) | |
END | |
SET @bytes = | |
CASE @delim | |
WHEN ':' THEN @vbytes | |
ELSE 0x000000000000000000000000 + @vbytes | |
END | |
RETURN @bytes | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment