THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
AS 
BEGIN 
   DECLARE 
@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 
   
ELSE 
       SET 
@i 
   
SET @odd CAST(LEN(SUBSTRING(@hexstr@iLEN(@hexstr))) % AS BIT)
   
WHILE (@i <= @count
    
BEGIN 
       IF 
@start AND @odd 1
       
BEGIN
           SET 
@hex '0' SUBSTRING(@hexstr@i1)
       
END
       ELSE
       BEGIN
           SET 
@hex SUBSTRING(@hexstr@i2)
       
END
       SET 
@b @b 
               
CAST(CASE WHEN SUBSTRING(@hex11) LIKE '[0-9]' 
                   
THEN CAST(SUBSTRING(@hex11AS INT
                   
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex11)))-55 AS INT
               
END 16 
               
CASE WHEN SUBSTRING(@hex21) LIKE '[0-9]' 
                   
THEN CAST(SUBSTRING(@hex21AS INT
                   
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex21)))-55 AS INT
               
END AS binary(1)) 
       
SET @i @i + (- (CAST(@start AS INT) * CAST(@odd AS INT)))
       
IF @start 1
       
BEGIN
           SET 
@start 0
       
END
    END 
    RETURN 
@b 
END 
GO 

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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: http://michaeldotnet.blogspot.com/2007/11/t-sql-hex-string-to-varbinary-improved.html

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

Chris

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.

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'

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

Jake!!!

December 2, 2008 5:41 AM
 

Adam Tappis said:

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

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.

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

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement