How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server?

When a query returns a binary string value to the client tool, it will be displayed by the client tool as hexadecimal digits in a character string format. But they are hexadecimal digits on the display window, not in the database storage.

Converting binary strings into hexadecimal character strings in storage is not an easy task, since there is not built-in functions to help you. You need to write a conversion algorithm yourself. This tutorial exercise, called bin2hex, shows you a simple example:

-- bin2hex 
DECLARE @binary_string VARBINARY(40);
DECLARE @hex_string VARCHAR(40);
DECLARE @position INT; -- Loop index
DECLARE @char INT; -- Character code value
DECLARE @half_char INT; -- Character code half value

-- Initial values
SET @binary_string = CONVERT(VARBINARY(40),'FYIcenter.com');
SET @hex_string = ''; -- Output hex strings
SET @position = 1;

-- Loop on each byte
WHILE @position <= DATALENGTH(@binary_string) BEGIN
   SET @char 
      = ASCII(SUBSTRING(@binary_string, @position, 1));

   -- Taking the first half byte 
   SET @half_char = @char / 16; -- left half
   IF @half_char < 10
      SET @hex_string = @hex_string 
         + CHAR(ASCII('0') + @half_char);
   ELSE 
      SET @hex_string = @hex_string 
         + CHAR(ASCII('A') + @half_char-10);

   -- Taking the second half byte 
   SET @half_char = @char % 16; -- left half
   IF @half_char < 10
      SET @hex_string = @hex_string 
         + CHAR(ASCII('0') + @half_char);
   ELSE 
      SET @hex_string = @hex_string 
         + CHAR(ASCII('A') + @half_char-10);

   -- Going the next iteration
   SET @position = @position+1;
   END
SELECT @hex_string;
GO
46594963656E7465722E636F6D

 

⇒Character Strings and Binary Strings in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials