Microsoft KB Archive/104829: Difference between revisions
(importing KB archive) |
m (Text replacement - "<" to "<") |
||
(One intermediate revision by the same user not shown) | |||
Line 78: | Line 78: | ||
select @i = 1 | select @i = 1 | ||
select @length = datalength(@binvalue) | select @length = datalength(@binvalue) | ||
select @hexstring = | select @hexstring = "0123456789abcdef" | ||
while (@i | while (@i <= @length) | ||
begin | begin | ||
Latest revision as of 14:45, 20 July 2020
Article ID: 104829
Article Last Modified on 2/22/2005
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 6.5 Service Pack 1
- Microsoft SQL Server 6.5 Service Pack 2
- Microsoft SQL Server 6.5 Service Pack 3
- Microsoft SQL Server 6.5 Service Pack 4
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 7.0 Service Pack 1
This article was previously published under Q104829
SUMMARY
SQL Server does not have built-in Transact-SQL command for converting binary data to a hexadecimal string.
MORE INFORMATION
The Transact-SQL CONVERT command converts binary data to character data in a one byte to one character fashion. SQL Server takes each byte of the source binary data, converts it to an integer value, then uses that integer value as the ASCII value for the destination character data. This behavior applies to the binary, varbinary, and timestamp datatypes.
For example, binary value 00001111 (0x0F in hexadecimal) is converted into its integer equivalent which is 15, then converted to the character that corresponds to ASCII value 15, which is unreadable.
The following stored procedure can be used to return a character string which contains the hexadecimal representation of a binary value:
create procedure sp_hexadecimal @binvalue varbinary(255) as declare @charvalue varchar(255) declare @i int declare @length int declare @hexstring char(16) select @charvalue = '0x' select @i = 1 select @length = datalength(@binvalue) select @hexstring = "0123456789abcdef" while (@i <= @length) begin declare @tempint int declare @firstint int declare @secondint int select @tempint = convert(int, substring(@binvalue,@i,1)) select @firstint = floor(@tempint/16) select @secondint = @tempint - (@firstint*16) select @charvalue = @charvalue + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1) select @i = @i + 1 end select 'sp_hexadecimal'=@charvalue
For example, when the following command batch is executed with ISQL:
declare @bin varbinary(255) select @bin = @@dbts execute sp_hexadecimal @bin select 'isql' = @bin go
it returns output similar to the following:
sp_hexadecimal ------------------------------------------------------------------ 0x01000000a60b0000 (1 row affected) isql ------------------------------------------------------------------ 0x01000000a60b0000 (1 row affected)
Additional query words: Windows NT
Keywords: kbinfo kbother KB104829