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
Â
⇒Character Strings and Binary Strings in SQL Server Transact-SQL
2018-06-19, 5655👍, 1💬
Popular Posts:
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...
How To See Which Storage Engines Are Supported in Your MySQL Server in MySQL? If you want to know ex...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...