T-SQL Hex String to VarBinary (Improved)
Peter DeBetta posted a while back with a function to take a hex string and convert it to varbinary It has a couple of slight issues, the biggest of which is it can't handle an odd number of hex digits. Below is my replacement, because it's using bigints the upper limit isn't as high, but it's good enough for most things:
CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @hex char(1), @i int, @place bigint, @a bigint
SET @i = LEN(@hexstr)
set @place = convert(bigint,1)
SET @a = convert(bigint, 0)
WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]'))
BEGIN
SET @hex = SUBSTRING(@hexstr, @i, 1)
SET @a = @a +
convert(bigint, CASE WHEN @hex LIKE '[0-9]'
THEN CAST(@hex as int)
ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
set @place = @place * convert(bigint,16)
SET @i = @i - 1
END
RETURN convert(varbinary(8000),@a)
END
GO
Subscribe to Michael.NET
9 comments:
Here is my version of a scalable Hex string conversion:
CREATE FUNCTION [dbo].[HexStrToVarBinary](@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @hexhigh char(1), @hexlow char(1), @i int, @hexbyte int, @iLen int, @a varbinary(8000)
SET @i = 1
SET @iLen = LEN(@hexstr)
WHILE (@i <= @iLen)
BEGIN
SET @hexhigh = SUBSTRING(@hexstr, @i, 1)
SET @hexlow = SUBSTRING(@hexstr, @i+1, 1)
IF @hexhigh LIKE '[0-9]'
SET @hexbyte = CAST(@hexhigh as int)
ELSE
BEGIN
IF @hexhigh LIKE '[A-F]'
SET @hexbyte = CAST(ASCII(@hexhigh)-55 as int)
ELSE
BEGIN
IF @hexhigh LIKE '[a-f]'
SET @hexbyte = CAST(ASCII(UPPER(@hexhigh))-55 as int)
ELSE
BREAK
END
END
SET @hexbyte = @hexbyte * 16
IF @i+1 <= @iLen
BEGIN
IF @hexlow LIKE '[0-9]'
SET @hexbyte = @hexbyte + CAST(@hexlow as int)
ELSE
BEGIN
IF @hexlow LIKE '[A-F]'
SET @hexbyte = @hexbyte + CAST(ASCII(@hexlow)-55 as int)
ELSE
BEGIN
IF @hexlow LIKE '[a-f]'
SET @hexbyte = @hexbyte + CAST(ASCII(UPPER(@hexlow))-55 as int)
ELSE
BREAK
END
END
END
IF @a IS NULL
SET @a = CAST(@hexbyte as binary(1))
ELSE
SET @a = @a + CAST(@hexbyte as binary(1))
SET @i = @i+2
END
RETURN @a
END
I should add that there is some redundancy there but it is included to make the function run faster. It isn't a very big function anyway!
--Edit by Ali Motamed Rezaie (motamedrezaie@gmail.com)
CREATE FUNCTION dbo.HexStrToVarBinary(
@hexstr VARCHAR(8000)
)RETURNS VARBINARY(8000) AS
BEGIN
DECLARE @ret VARBINARY(8000);
DECLARE @chr1 CHAR(1);
DECLARE @chr2 CHAR(1);
DECLARE @i INT;
DECLARE @len INT;
SET @len = LEN(@hexstr);
SET @i = 1;
SET @ret = CAST('' AS VARBINARY);
SET @chr1 = SUBSTRING(@hexstr, @i, 1);
SET @chr2 = SUBSTRING(@hexstr, @i + 1, 1);
WHILE (@i <= @len AND @chr1 LIKE '[0-9A-Fa-f]' AND @chr2 LIKE '[0-9A-Fa-f]')
BEGIN
SET @chr1 = SUBSTRING(@hexstr, @i, 1);
SET @chr2 = SUBSTRING(@hexstr, @i + 1, 1);
SET @ret = @ret + CAST(((CASE WHEN @chr1 LIKE '[0-9]' THEN CAST(@chr1 AS TINYINT)
ELSE CAST(ASCII(UPPER(@chr1)) - 55 AS TINYINT)
END * CAST(16 AS TINYINT))
+ CASE WHEN @chr2 LIKE '[0-9]' THEN CAST(@chr2 AS TINYINT)
ELSE CAST(ASCII(UPPER(@chr2)) - 55 AS TINYINT)
END)
AS VARBINARY);
SET @i = @i + 2;
END
RETURN @ret;
END
I might suggest one additional test for valid HEX by changing:
WHILE (@i <= @len AND @chr1 LIKE '[0-9A-Fa-f]' AND @chr2 LIKE '[0-9A-Fa-f]')
to
WHILE (@i <= @len AND @chr1 LIKE '[0-9A-Fa-f]' AND @chr2 LIKE '[0-9A-Fa-f]' AND (@chr2 % 2 = 0))
When converting a column that was supposed to be all hex passwords, I hit 'feb0102' which failed because it was not even... doh
-Kael
Oops.. correction!
I meant
WHILE (@i <= @len AND @chr1 LIKE '[0-9A-Fa-f]' AND @chr2 LIKE '[0-9A-Fa-f]' AND (@len % 2 = 0))
-Kael
Hmmm... I wonder why all the worry about an odd number of "hexits" in T-SQL? They cannot be stored as such in a VarBinary or Binary.
--Jeff Moden
DECLARE @ASCIIHex VARCHAR(16)
SET @ASCIIHex = 'ABCD0100FF'
DECLARE @Result VARBINARY(8000) = CONVERT(TINYINT,0)
SELECT
@Result = @Result +
Convert(varbinary, SUBSTRING(@ASCIIHex, number, 2), 2)
FROM
master.dbo.spt_values numbers WITH(NOLOCK)
WHERE
numbers.Type = 'P'
AND (numbers.number % 2)=1
AND numbers.number BETWEEN 1 AND LEN(@ASCIIHex)
SELECT SUBSTRING(@Result,2, LEN(@Result)-1)
My version, with some additional protections for negative, odd numbers of digitis passed in and for strings with the Hex notation on the front.
CREATE FUNCTION dbo.fn_Hex2Int
(
@ASCIIHex VARCHAR(18)
)
RETURNS BIGINT
/*
***********************************************************************************************************************
Title: fn_Hex2Int
Date: 13/10/2021
By: Aaron Reese (aaron.reese@E4-consulting.co.uk)
Details:
based on a suggestion in this post: http://michaeldotnet.blogspot.com/2007/11/t-sql-hex-string-to-varbinary-improved.html
Takes a string representation of the hex value and converts the result to an signed integer
***********************************************************************************************************************
***********************************************************************************************************************
***********************************************************************************************************************
*/
AS
BEGIN
DECLARE @Multiplier INT = 1
/*DEBUG*/
--DECLARE @ASCIIHex VARCHAR(16)
--SET @ASCIIHex = '0x-00AB000f'
/*END DEBUG*/
/*Remove the hex marker if it exists...*/
SET @ASCIIHex = REPLACE(@ASCIIHex,'0x','')
/*check if value is negative and reset the multiplier*/
IF LEFT(@ASCIIHex,1) = '-' BEGIN
SET @Multiplier = -1
SET @ASCIIHex = REPLACE(@ASCIIHex,'-','')
END
/*Check the string is an even length and add a leading 0 if not*/
IF LEN(@ASCIIHex) % 2 = 1 BEGIN
SET @ASCIIHex = '0' + @ASCIIHex
END
DECLARE @Result VARBINARY(8000) = CONVERT(TINYINT,0)
SELECT
@Result = @Result +
Convert(varbinary, SUBSTRING(@ASCIIHex, number, 2), 2)
FROM
master.dbo.spt_values numbers WITH(NOLOCK)
WHERE
numbers.Type = 'P'
AND (numbers.number % 2)=1
AND numbers.number BETWEEN 1 AND LEN(@ASCIIHex)
RETURN (SELECT CONVERT(INT,SUBSTRING(@Result,2, LEN(@Result)-1)) * @Multiplier)
END
GO
/*
Ali Motamed Rezaie
*/
CREATE FUNCTION fnHexUnicodeToNVarChar (
@HexUnicode NVARCHAR(MAX)
) RETURNS NVARCHAR(MAX) AS
BEGIN
DECLARE @p INT = CHARINDEX(N'%u', @HexUnicode, 0),
@u NVARCHAR(4) = N'';
WHILE @p > 0
BEGIN
SET @u = SUBSTRING(@HexUnicode, @p + 2, 4);
IF @u LIKE '[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]'
SET @HexUnicode = REPLACE(@HexUnicode, CONCAT(N'%u', @u), NCHAR(CONVERT(VARBINARY(2), @u, 2)))
SET @p = CHARINDEX(N'%u', @HexUnicode, @p + 1);
END
RETURN @HexUnicode;
END -- fnHexUnicodeToNVarChar
Post a Comment