Collections:
bin2hex - Converting Binary Strings into Hexadecimal Character Strings in SQL Server
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server?
✍: FYIcenter.com
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
⇒ AND, OR, XOR, and NOT - Bitwise Operations in SQL Server
⇐ Converting Binary Strings into Unicode Character Strings in SQL Server
⇑ Character Strings and Binary Strings in SQL Server Transact-SQL
2018-06-19, 7142🔥, 1💬
Popular Posts:
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Look at the Current SQL*Plus System Settings in Oracle? If you want to see the current values...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...