T-SQL Convert Hex String to Varbinary

This is really usefull. It also works in MS SQL 2000

'15E' returns 5577 instead of 350

It's important to note that it requires an even number of hexits, so for instance '015E' DOES return 350

I posted an updated version on my blog, it handles odd numbers of hex digits (and stops at the first non hex character rather then looking for "0x").  It uses bigints though so is somewhat limited: http://michaeldotnet.blogspot.com/2007/11/t-sql-hex-string-to-varbinary-improved.html

it works much better i "SET @b = @b +..." is more correctly "SET @b = @b * 256 +..."  This is needed to left shift the value of b clearing out the lower byte for the next conversion.   Otherwise this only works for a byte as in 0x12, but would not work for 0x12345678

Chris

Riddle me this...

declare @tmp varchar(8)

set @tmp = '0x514'

select dbo.HexStrToVarBin(@tmp),sys.fn_varbintohexstr(dbo.HexStrToVarBin(@tmp))

input doesnt match output ... am I missing the obvious ... ?

Instead of sys.fn_varbintohexstr, Sql Server 2008 now can do this natively via CONVERT.

e.g.

declare @testbin varbinary(4)

declare @string varchar(MAX)

-- The third parameter to CONVERT selects the type of conversion

-- 0 does a direct ASCII character cast.

-- 1 does hex string with 0x prepended

-- 2 does hex string without 0x prepended

set @testbin = 0x0011

set @string = CONVERT(varchar(MAX), @testbin, 2)

select @testbin as 'varbin',@string as 'hexstring'

Thanks a lot.It helped me to step forward and I found it is very helpful.

Yes Peter I did the same thing shortly before I tested the beta of 2008 then decided to wait and use the native one! It's good that these issues are being addressed.

i just wanted to say THANK YOU SOOO MUCH for this, i was just about to give up and stumbled across this post...

thank god for YOU

cheers mate

Jake!!!

Here's another option using built in XML functions from the SQL Enginer Tips blog http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

Working perfect :) great work...

I achived the same result by renaming the existing field, creating a new field as type VARCHAR and then running the following SQL:

UPDATE <table_name>

SET <new_field> = REPLACE(CAST(<old_field> AS VARCHAR(1000)), CHAR(0), '')

WHERE <old_field> IS NOT NULL

Four years later and I want to thank you for your post. I was scratching my head trying to query a table with a column of varbinary type.

Your function came very handy.

Here's a quick implementation of VarBinToHexStr for SQL Server 2000 (I gather there's a Stored Procedure, but having a FUNCTION implementation is useful . . .)

Note that I have not debugged this extensively, use at your own risk, I'm not the world's most fluent TSQL coder, etc., etc.

DROP FUNCTION dbo.VarBinToHexStr

GO

CREATE FUNCTION dbo.VarBinToHexStr(@varbin varbinary(3999))  

RETURNS varchar(8000)  

AS  

BEGIN

 DECLARE @hexstr varchar(8000), @hex char(2), @i int, @msc int, @lsc int, @count int

 SET @hexstr = '0x'

 SET @count = LEN(@varbin)

 SET @i = 1

 WHILE (@i <= @count)

 BEGIN

   SET @msc = FLOOR(CAST(SUBSTRING(@varbin, @i, 1) AS int) / 16)

   SET @lsc = CAST(SUBSTRING(@varbin, @i, 1) AS int) % 16

   SET @hexstr = @hexstr + CASE WHEN @msc >= 10 THEN CHAR(@msc + 55) ELSE CAST(@msc AS varchar(1)) END + CASE WHEN @lsc >= 10 THEN CHAR(@lsc + 55) ELSE CAST(@lsc AS varchar(1)) END

   SET @i = @i+1

 END

 RETURN @hexstr

END

GO

For some reason I was drawing a blank yesterday morning on the proper name for half a byte, so I used lsc and msc as variables names (standing for least and most significant chunk).  This morning my brain worked properly and I remembered that it's a nibble (nybble is just ugly), so lsn and msn are the appropriate variable names.  Doesn't really affect the code, just the aesthetics.