Created
December 14, 2017 11:41
-
-
Save anonymous/c548fd99c127ba02b67dad3255f3271f to your computer and use it in GitHub Desktop.
Convertion of binary representation of ip addresses into varchar.
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_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2) | |
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF) | |
ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress] | |
( | |
@bytes BINARY(16) | |
) | |
RETURNS VARCHAR(39) AS | |
BEGIN | |
DECLARE | |
@part VARBINARY(2) | |
, @colIndex TINYINT | |
, @ipAddress VARCHAR(39) | |
SET @ipAddress = '' | |
IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000 | |
BEGIN | |
SET @colIndex = 13 | |
WHILE @colIndex <= 16 | |
SELECT | |
@part = SUBSTRING(@bytes, @colIndex, 1) | |
, @ipAddress = @ipAddress | |
+ CAST(CAST(@part AS TINYINT) AS VARCHAR(3)) | |
+ CASE @colIndex WHEN 16 THEN '' ELSE '.' END | |
, @colIndex = @colIndex + 1 | |
IF @ipAddress = '0.0.0.1' | |
SET @ipAddress = '::1' | |
END | |
ELSE | |
BEGIN | |
SET @colIndex = 1 | |
WHILE @colIndex <= 16 | |
BEGIN | |
SET @part = SUBSTRING(@bytes, @colIndex, 2) | |
SELECT | |
@ipAddress = @ipAddress | |
+ CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)') | |
+ CASE @colIndex WHEN 15 THEN '' ELSE ':' END | |
, @colIndex = @colIndex + 2 | |
END | |
END | |
RETURN @ipAddress | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment