### 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)ASBEGIN     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)ENDGO `

#### 7 comments: Anonymous said...

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 Anonymous said...

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!

Ali said...

--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 Anonymous said...

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 Anonymous said...

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 Jeff Moden said...

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 Anonymous said...

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)