March 14, 2007 at 6:07 pm
Hi,
I have table column with binary(8) data type that has hexadecimal value like 0x0000000000000162
Now I have to use that value in front end application . When I convert it to Varchar(8000) . it doesn't show up in select or in variable. Can someone tell me how I convert this value so that I can use it in front end application like C#.
if I get that value without any convert than I get system.bye[] like that in front end and dont know how to read this.
Note: Can not use it like CONVERT(varchar(8000), 0x0000000000000162) or '0x0000000000000162'. Both cases not working
Thanks
March 14, 2007 at 6:53 pm
Why varchar(8000)?
binary(8) cannot return string longer than 8 characters.
Then, there is no printable character corresponding to the code 0x0000000000000162.
Do you have any idea about meaning of the data you are trying to retrieve?
From the length of the datatype I can conclude that it's rather bigint value.
CONVERT(bigint, 0x0000000000000162)
Brings reasonable result.
_____________
Code for TallyGenerator
March 15, 2007 at 8:27 am
I think that the reason you get no output is that the leading 0x00 is interpreted as an end-of-string marker.
0x62 is ascii lower case b, and 0x01 is control-a, but you don't see it becuase the 0x00 ends the string.
You probably need to ask youself what this value is supposed to look like to the user, and then figure out how to get it converted.
March 15, 2007 at 10:45 am
Does the string representation need to be hex? If decimal is ok, look at this example:
DECLARE @bin binary(8)
SET @bin = 0x7FFFFFFFFFFFFFFF
SELECT @bin AS binaryValue
, CONVERT(bigint, @bin) AS BigIntValue
, CONVERT(varchar(20), 0x0000000000000162) AS varcharValue
, CONVERT(varchar(20), CONVERT(bigint, @bin)) AS FinalCharValue
March 15, 2007 at 10:55 am
If you have a function that converts int/binary to a hex string (type varchar), such the one shown below, you could do this to return a string representation of the hex binary value:
DECLARE @bin binary(8)
SET @bin = 0x7FFFFFFFFFFFFFFF
SELECT @bin AS binaryValue
, dbo.fnInt2Hex(@bin, 0) AS HexValue
, dbo.fnInt2Hex(@bin, 20) AS HexValuePadded
------------------------------------------------------------------------------
CREATE FUNCTION dbo.fnInt2Hex
(
@num bigint
, @padLength int = NULL
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @rem bigint, @base bigint
DECLARE @hexdigits varchar(16)
DECLARE @hex varchar(20)
IF @num = 0
RETURN '0'
SET @base = 16
SET @hexdigits = '0123456789ABCDEF'
SET @hex = ''
WHILE @num <> 0
BEGIN
SET @rem = @num % @base
SET @num = @num / @base
SET @hex = Substring(@hexdigits, @rem + 1, 1) + @hex
END
IF @padLength IS NOT NULL
IF @padLength > Len(@hex)
SET @hex = Right(Replicate('0',@padLength) + @hex, @padLength)
RETURN @hex
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply