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, 8162🔥, 1💬
Popular Posts:
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...