Garfy
asked on
Converting a Binary to a Varchar
I am trying to convert a binary number to a varchar so I can include it into a string. If do a straight Convert then I get blank, if I convert to an integer I get a different number. Help tells me that converting from a binary to an integer and then converting back to a binary will not result in the same binary number that you started out with.
I need to know if anyone has been able to successfully convert a binary to a varchar and back again, and how they did it.....
I need to know if anyone has been able to successfully convert a binary to a varchar and back again, and how they did it.....
ASKER
Read the question again, I have already tried that, and Microsoft themselves do not recommend this course of action, but they do not give a solution......
ASKER
Read the question again, I have already tried that, and Microsoft themselves do not recommend this course of action, but they do not give a solution......
ASKER
print @CompanyID returns 0x0000000000002E66
set @Message = 'Company No: ' + Convert(varchar, Cast (@CompanyID as bigint))
Print @Message
returns
Company No: 11878
set @Message = 'Company No: ' + Convert(varchar, Cast (@CompanyID as bigint))
Print @Message
returns
Company No: 11878
Garfy,
Well, I am not sure of your purpose, but it is an interesting challenge, so I wrote some code for you. Here it is:
declare @CompanyID binary(8)
declare @Byte int
declare @Digit int
declare @String varchar(30)
declare @I int
select @CompanyID = 0x0000000000002E66
select @I=1
select @String = 'Value is 0x'
while @I < 9
begin
select @Byte = substring(@CompanyID, @I, 1)
select @Digit = floor(@Byte / 16)
select @String = @String + substring('0123456789ABCDE F', @Digit + 1, 1)
select @Digit = @Byte % 16
select @String = @String + substring('0123456789ABCDE F', @Digit + 1, 1)
select @I = @I + 1
end
select @string as 'Converted String'
This code returns the following:
Converted String
-------------------------- ----
Value is 0x0000000000002E66
(1 row(s) affected)
BTW, the value 0x0000000000002E66 is a hexadecimal number equal to 11878 decimal as your query shows. You can confirm it by using windows calculator in scientific mode to convert the decimal number to hex.
Hope this helps.
-Bob
Well, I am not sure of your purpose, but it is an interesting challenge, so I wrote some code for you. Here it is:
declare @CompanyID binary(8)
declare @Byte int
declare @Digit int
declare @String varchar(30)
declare @I int
select @CompanyID = 0x0000000000002E66
select @I=1
select @String = 'Value is 0x'
while @I < 9
begin
select @Byte = substring(@CompanyID, @I, 1)
select @Digit = floor(@Byte / 16)
select @String = @String + substring('0123456789ABCDE
select @Digit = @Byte % 16
select @String = @String + substring('0123456789ABCDE
select @I = @I + 1
end
select @string as 'Converted String'
This code returns the following:
Converted String
--------------------------
Value is 0x0000000000002E66
(1 row(s) affected)
BTW, the value 0x0000000000002E66 is a hexadecimal number equal to 11878 decimal as your query shows. You can confirm it by using windows calculator in scientific mode to convert the decimal number to hex.
Hope this helps.
-Bob
ASKER
Sadly, by using substring, and I was still not able to add the binary number to another string....... it keeps saying that you cannot add a string and binary..... I am using SQL 2000. Microsoft appear to be aware of a problem with binaries and strings but they have not posted a resolution. I am hoping someone here has one...
All I want to do is run these lines
************************** ********** ********** **********
/*set @Message = ('Update ' + @Tname + ' set ' + @Fname + ' = ' + Convert(varchar,@CompanyID ) + ' where ' + @Fname + ' = ' + CONVERT(varchar, @ContactID))
exec (@Message)
************************** ********** ********** ********** *
CompanyID and ContactID are binary numbers.......
All I want to do is run these lines
**************************
/*set @Message = ('Update ' + @Tname + ' set ' + @Fname + ' = ' + Convert(varchar,@CompanyID
exec (@Message)
**************************
CompanyID and ContactID are binary numbers.......
Garfy,
If this what you looking for:
declare @a binary(16)
select @a = 0x0000000000002E66
select @a, '<' + dbo.fn_sqlvarbasetostr(@a) + '>'
output:
0x0000000000002E6600000000 00000000 <0x0000000000002e660000000 000000000>
Uri
If this what you looking for:
declare @a binary(16)
select @a = 0x0000000000002E66
select @a, '<' + dbo.fn_sqlvarbasetostr(@a)
output:
0x0000000000002E6600000000
Uri
or even better use dbo.fn_varbintohexstr(@a)
ASKER
I got so excited.... but then I got this error message
Server: Msg 208, Level 16, State 1, Procedure rc_spCompIDsameContIDnotOr phans, Line 143
Invalid object name 'dbo.fn_varbintohexstr'.
Server: Msg 208, Level 16, State 1, Procedure rc_spCompIDsameContIDnotOr
Invalid object name 'dbo.fn_varbintohexstr'.
Garfy,
I am only using SQL 7 and I don't know why you are having so much trouble with SQL 2000. Anyway, here's a hack which works on SQL 7 and doesn't use substring on binary data:
declare @CompanyID binary(8)
declare @Binary binary(4)
declare @Integer int
declare @Decimal decimal(20)
declare @Digit int
declare @String varchar(30)
declare @I int
select @CompanyID = 0x0000000000002E66
select @Binary = @CompanyID
select @Integer = @Binary
select @Decimal = convert(decimal, @Integer) * power(convert(decimal,2), 32)
select @Integer = @CompanyID
select @Decimal = @Decimal + convert(decimal, @Integer)
if @Integer < 0 select @Decimal = @Decimal + power(convert(decimal, 2), 32)
select @I=1
select @String = ''
while @I < 17
begin
select @Digit = @Decimal - floor(@Decimal / 16) * 16
select @String = substring('0123456789ABCDE F', @Digit + 1, 1) + @String
select @Decimal = floor(@Decimal / 16)
select @I = @I + 1
end
Select @String = 'Value is 0x' + @String
select @string as 'Converted String'
Converted String
-------------------------- ----
Value is 0x0000000000002E66
(1 row(s) affected)
It only works with data up to 8 bytes (2 integers) long, but it could be extended by adding more interim binary variables. Not elegant code, but may help.
-Bob
I am only using SQL 7 and I don't know why you are having so much trouble with SQL 2000. Anyway, here's a hack which works on SQL 7 and doesn't use substring on binary data:
declare @CompanyID binary(8)
declare @Binary binary(4)
declare @Integer int
declare @Decimal decimal(20)
declare @Digit int
declare @String varchar(30)
declare @I int
select @CompanyID = 0x0000000000002E66
select @Binary = @CompanyID
select @Integer = @Binary
select @Decimal = convert(decimal, @Integer) * power(convert(decimal,2), 32)
select @Integer = @CompanyID
select @Decimal = @Decimal + convert(decimal, @Integer)
if @Integer < 0 select @Decimal = @Decimal + power(convert(decimal, 2), 32)
select @I=1
select @String = ''
while @I < 17
begin
select @Digit = @Decimal - floor(@Decimal / 16) * 16
select @String = substring('0123456789ABCDE
select @Decimal = floor(@Decimal / 16)
select @I = @I + 1
end
Select @String = 'Value is 0x' + @String
select @string as 'Converted String'
Converted String
--------------------------
Value is 0x0000000000002E66
(1 row(s) affected)
It only works with data up to 8 bytes (2 integers) long, but it could be extended by adding more interim binary variables. Not elegant code, but may help.
-Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you thank you thank you. You are a marvel. Is there a function that does the reverse??????
ASKER
thank you thank you thank you. You are a marvel. Is there a function that does the reverse??????
I hunted for a way to concatenate a binary field with text.
Master.dbo.fn_varbintohexs tr worked for me!
Master.dbo.fn_varbintohexs
as in the next example:
declare @a binary(16), @b bigint
select @a = 2147483647
select @b = cast (@a as int)
select @a, @b
Regards,
Uri