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, 6905🔥, 1💬
Popular Posts:
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...