Problem

I have a requirement to convert the binary data inside a table column or variable to a hexadecimal string. Is it possible to directly convert the binary data to a hexadecimal string characters?

Solution

Before SQL Server 2005, it was not possible to directly convert the binary data to a string of hexadecimal characters, however, since SQL Server 2005; you can do the binary to hexadecimal conversion with one of the following two Transact-SQL approaches:

Option 1: Using CONVERT function of Transact-SQL

In SQL Server 2008 and later versions, you can use the Transact-SQL CONVERT function with its one of the following two binary styles to convert the binary data into a hexadecimal string:

Microsoft Reference: http://msdn.microsoft.com/en-GB/library/ms187928.aspx

The following is the basic syntax for CONVERT function:

CONVERT ( destination_data_type [ ( length ) ] , expression, style )

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the CONVERT function with binary styles as follows:

SET NOCOUNT ON;
DECLARE @BinaryValue VARBINARY(256) 
SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00
SELECT  CONVERT([varchar](512), @BinaryValue, 1)
SELECT  CONVERT([varchar](512), @BinaryValue, 2) 
SET NOCOUNT OFF;

It returns the following output:

For more information, see CAST and CONVERT (Transact-SQL).

Option 2: Using xml value() Method with the XQUERY functions

In SQL Server 2005 and later versions, you can use xml value() method with the XQUERY functions to convert the binary data into a hexadecimal string. The xml value() method let you extract the value of a specified XML node as a scalar value and then converts the value to a specified SQL Server data type. You can use SQL Server XQuery Extension Functions and xs:hexBinary base type Constructor Function within a xml value() method to convert the binary data into a hexadecimal string. The SQL Server XQuery Extension Functions includes sql:column() and sql:variable() functions, which can be used to expose a relational value inside an XQuery expression. For example, sql:variable() Function exposes a variable that contains a SQL relational value inside an XQuery expression while sql:column() Function exposes a column that contains a SQL relational value inside an XQuery expression. The xs:hexBinary base type Constructor Function creates the hexadecimal character sequence.

The following is the basic syntax of the value() method with the XQUERY functions to convert the binary data into a hexadecimal string:

value(xs:hexBinary(sql:variable("VariableName") , SQLType)
-- or -- 
value(xs:hexBinary(sql:column("ColumnName") , SQLType)

For example, suppose you want to convert the following binary value to hexadecimal string: 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00. To do that, you use the xml value() Method with the XQUERY functions as follows:

SET NOCOUNT ON;
DECLARE @BinaryValue[varbinary](256) ,
@x[xml]
SET @x = '<root></root>;'
SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00
SELECT  N'0x' + @x.value('xs:hexBinary(sql:variable("@BinaryValue"))',
                         '[varchar](512)')
SET NOCOUNT OFF;
GO

It returns the following output:

Option 3: Using sys.fn_varbintohexstr undocumented function and sp_hexdecimal stored procedure

You can use sys.fn_varbintohexstr undocumented function to return a character string which contains the hexadecimal representation of a binary value. For example, the following script shows how you can use sys.fn_varbintohexstr undocumented function to convert binary value to a hexadecimal string:

SET NOCOUNT ON;
DECLARE @BinaryValue [varbinary](256); 
SET @BinaryValue = 0x0200CDDBC2A60A08B57EDF7622CA52A45EC3BC234CE6A0F7D038F3BAE0EC00;
SELECT  [sys].[fn_varbintohexstr](@BinaryValue);
SET NOCOUNT OFF;
GO

It returns the following output:

You can also use sp_hexdecimal stored procedure as described in a Microsoft Knowledge Base Article: “INFO: Converting Binary Data to Hexadecimal String” to convert binary value to a hexadecimal string.

My preferred option

I prefer option 1 and 2 mentioned in this post for converting the binary value to a hexadecimal string. That is why; I wrote the following function using the first two options, which you can use to return a string containing the hexadecimal representation of a binary value:

IF OBJECT_ID(N'dbo.ufn_binvaluetohexdecstr') IS NOT NULL
    BEGIN
        DROP FUNCTION [dbo].[ufn_binvaluetohexdecstr];
    END
GO
CREATE FUNCTION [dbo].[ufn_binvaluetohexdecstr] (@p_binhexvalue [varbinary](256))
RETURNS [varchar](512)
AS
    BEGIN
        DECLARE @x[xml] ,
@OutPutStrHex[varchar](512) ,
@Version[numeric](18, 1);
SET @x = '<root></root>';
        SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.'
+ REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)),
LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))
- CHARINDEX(N'.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10));
        
        IF @Version >= 10.5
            BEGIN 
                SELECT  @OutPutStrHex = CONVERT([varchar](512), @p_binhexvalue, 1);
            END
        ELSE
            BEGIN 
                SELECT  @OutPutStrHex = N'0x' + @x.value('xs:hexBinary(sql:variable("@p_binhexvalue"))',
                                                         '[varchar](512)');
            END

        RETURN (SELECT @OutPutStrHex) 
    END
GO

I hope you will find this post useful.

Have a good week!

Basit