THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

T-SQL Convert Hex String to Varbinary

[EDIT] I made a few changes to account for the issues Chris and Cooter noted in the comments. 

One last post before I go on vacation next week...

SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0x3a becomes '0x3a'), but there is no function to go back from a hex string to a hex value. Now there is J:

CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(8000)) 
RETURNS varbinary(8000
@hex CHAR(2), @i INT@count INT@b varbinary(8000), @odd BIT@start bit
SET @count LEN(@hexstr
SET @start 1
SET @b CAST('' AS varbinary(1)) 
IF SUBSTRING(@hexstr12'0x' 
SET @i 
SET @odd CAST(LEN(SUBSTRING(@hexstr@iLEN(@hexstr))) % AS BIT)
WHILE (@i <= @count
@start AND @odd 1
@hex '0' SUBSTRING(@hexstr@i1)
@hex SUBSTRING(@hexstr@i2)
@b @b 
END 16 
END AS binary(1)) 
SET @i @i + (- (CAST(@start AS INT) * CAST(@odd AS INT)))
IF @start 1
@start 0

Published Friday, March 9, 2007 3:38 PM by Peter W. DeBetta
Filed under:



Kedar Pathak said:

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

April 9, 2007 5:39 AM

wally said:

'15E' returns 5577 instead of 350

October 31, 2007 9:12 AM

MichaelDotNet said:

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

November 13, 2007 12:03 PM

MichaelDotNet said:

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:

November 13, 2007 3:20 PM

Chris said:

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


August 15, 2008 3:06 PM

Cooter said:

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 ... ?

August 26, 2008 10:44 PM

Peter W. DeBetta said:

I changed the post to include the code with the fix for the issues both Chris and Coote noted.

August 27, 2008 3:26 AM

SamCPP said:

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


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'

October 15, 2008 9:15 PM

Peter W. DeBetta said:

SamCPP - Great point. How quickly our code becomes antiquated.

Back in SQL Server 2005 days, I had implemented a UDT that is similar in function to the new HIERARCHYID data type in 2008.

October 15, 2008 9:59 PM

Sowjanya said:

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

October 16, 2008 3:32 AM

SamCPP said:

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.

November 21, 2008 12:44 AM

Jake said:

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


December 2, 2008 5:41 AM

Adam Tappis said:

Here's another option using built in XML functions from the SQL Enginer Tips blog

December 31, 2008 9:50 AM

usmanbinasif said:

Working perfect :) great work...

December 31, 2010 7:07 AM

Nick said:

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

February 28, 2011 8:28 PM

Mauricio Villablanca said:

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.

July 30, 2011 4:38 AM

Toby Ovod-Everett said:

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.



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

RETURNS varchar(8000)  



 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)


   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


 RETURN @hexstr



January 7, 2012 11:05 AM

Toby Ovod-Everett said:

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.

January 9, 2012 5:11 PM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement