Tuesday, November 13th, 2007

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
Tags: , , , | Comments RSS

2 Comments on “T-SQL Hex String to VarBinary (Improved)”

  1. Jul 2, 2008 - 4:52 am
    Burhan Altundal

    I need your function please give me it.

  2. Aug 11, 2008 - 4:14 pm
    Chris Fox

    Microsoft says that we should not use the undocumented Binary-to-Hex function because it could go away at any time. Does anyone have a TSQL function like the above by for Binary-To-hex, so I don’t have to use the undocumented function?

Leave a Reply