Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - bin2hex - Converting Binary Strings into Hexadecimal Character Strings

By: FYIcenter.com

(Continued from previous topic...)

How To Convert Binary Strings into Hexadecimal Character Strings

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

(Continued on next topic...)

1. How To Concatenate Two Character Strings Together?
2. What Happens When Unicode Strings Concatenate with Non-Unicode Strings?
3. How To Convert a Unicode Strings to Non-Unicode Strings?
4. What Are the Character String Functions Supported by SQL Server 2005?
5. How To Insert New Line Characters into Strings?
6. How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?
7. How To Concatenate Two Binary Strings Together?
8. Can Binary Strings Be Used in Arithmetical Operations?
9. How To Convert Binary Strings into Integers?
10. Can Binary Strings Be Converted into NUMERIC or FLOAT Data Types?
11. Can Binary Strings Be Converted into Character Strings?
12. Can Binary Strings Be Converted into Unicode Character Strings?
13. How To Convert Binary Strings into Hexadecimal Character Strings
14. What Are Bitwise Operations?