SQL Server T-SQL User-Defined Function of the Week

Converting to HEX with fn_varbintohexstr

Volume 1 Number 26    May 13, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

This issue continues with our study of the undocumented system
UDFs.  This week it's fn_varbintohexstr, a function that can
be used to convert almost any data to a hex representation. The
syntax for the function call is:

        master.dbo.fn_varbintohexstr (@Input VarBinary(8000))
        
The three part name with the prefix master.dbo is required when
you use fn_varbintohexstr in any database other than master.  
That's because it's not a real system UDF, it's owned by dbo in
the master database. When working in master, 
dbo.fn_varbintohexstr is sufficient.

The @input argument is declared varbinary(8000) and there is no
automatic conversion.  That means that unless the input starts
out as varbinary, you'll have to CAST or CONVERT it.  That's not
really difficult.  

The declared length of the @Input parameter is misleading.  Each
byte is going to be turned into two Hex characters of Unicode
output.  Since SQL Server will only output 4000 Unicode 
characters in any output string, the effective limit on the
size of @Input is 2000 characters.

The function returns a nvarchar(4000) string which represents the
@Input as Hex. Each byte is represented by two characters.
A pretty simple example is:

/--------- Start Copying from below this line ------------------\
-- Simple use of fn_varbintohexstr
select master.dbo.fn_varbintohexstr(CAST('ABCD' as varbinary)) 
                                   as Hex
go
\--------- Stop Copying above this line ------------------------/
(Results)
Hex              
-----------------------------------------------------------------
0x41424344

Now is the time to drag out your ASCII charts and check to be 
sure it's correct.  You don't really have to bother, I checked
and it's correct.  Here's another example with different data 
types:

/--------- Start Copying from below this line ------------------\
-- Show a variety of data types being converted to varbinary
SELECT N'   ASCII Characters  ABCD ->' 
       + master.dbo.fn_varbintohexstr(CAST('ABCD' as varbinary))
               + '<-' as Demo
UNION SELECT N' Unicode Characters  ABCD ->' 
       + master.dbo.fn_varbintohexstr(CAST(N'ABCD' as varbinary))
               + '<-'
UNION SELECT N'              Integer 100 ->' 
       + master.dbo.fn_varbintohexstr(CAST(100 as varbinary)) 
               + '<-'
UNION SELECT N'                    BIT 1 ->' 
       + master.dbo.fn_varbintohexstr(CAST(CAST (1 as BIT) 
               as varbinary)) + '<-'
UNION SELECT N'       Numeric (18,3) 100 ->' 
           + master.dbo.fn_varbintohexstr
                 (CAST(CAST (100 as numeric(18,3)) as varbinary))
              + '<-'
UNION SELECT N'                float 100 ->' 
           + master.dbo.fn_varbintohexstr
                  (CAST(CAST (100 as float) as varbinary)) + '<-'
GO
\--------- Stop Copying above this line ------------------------/
(Results)
Demo 
-----------------------------------------------------------------
                    BIT 1 ->0x01<-                
                float 100 ->0x4059000000000000<-
              Integer 100 ->0x00000064<-
       Numeric (18,3) 100 ->0x12030001a0860100<-
   ASCII Characters  ABCD ->0x41424344<-
 Unicode Characters  ABCD ->0x4100420043004400<-
         
One of the useful things that can be done with 
fn_varbintohexstr is to produce a readable hex dump of a string.
This helps you find embedded carriage returns, or accented 
characters that may not be displayed correctly elsewhere.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+