Solved

Converting a Binary to a Varchar

Posted on 2002-04-14
14
2,186 Views
Last Modified: 2012-08-13
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.....
0
Comment
Question by:Garfy
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 4

Expert Comment

by:urim
ID: 6941277
Why not use cast (@a as int)

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
0
 
LVL 1

Author Comment

by:Garfy
ID: 6943433
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......
0
 
LVL 1

Author Comment

by:Garfy
ID: 6943434
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......
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:Garfy
ID: 6943488
print @CompanyID returns 0x0000000000002E66


set @Message = 'Company No: ' + Convert(varchar, Cast (@CompanyID as bigint))
Print @Message

returns

Company No: 11878
0
 
LVL 3

Expert Comment

by:bobj_kirk
ID: 6943955
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('0123456789ABCDEF', @Digit + 1, 1)
  select @Digit = @Byte % 16
  select @String = @String + substring('0123456789ABCDEF', @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

0
 
LVL 1

Author Comment

by:Garfy
ID: 6944012
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.......
0
 
LVL 4

Expert Comment

by:urim
ID: 6944036
Garfy,

If this what you looking for:

declare @a binary(16)
select @a = 0x0000000000002E66
select @a, '<' + dbo.fn_sqlvarbasetostr(@a) + '>'

output:
0x0000000000002E660000000000000000     <0x0000000000002e660000000000000000>

Uri
0
 
LVL 4

Expert Comment

by:urim
ID: 6944045
or even better use dbo.fn_varbintohexstr(@a)
0
 
LVL 1

Author Comment

by:Garfy
ID: 6944081
I got so excited....  but then I got this error message

Server: Msg 208, Level 16, State 1, Procedure rc_spCompIDsameContIDnotOrphans, Line 143
Invalid object name 'dbo.fn_varbintohexstr'.
0
 
LVL 3

Expert Comment

by:bobj_kirk
ID: 6944173
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('0123456789ABCDEF', @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
0
 
LVL 4

Accepted Solution

by:
urim earned 200 total points
ID: 6944179
write master.dbo.fn_varbintohexstr
0
 
LVL 1

Author Comment

by:Garfy
ID: 6946439
thank you thank you thank you.  You are a marvel.  Is there a function that does the reverse??????
0
 
LVL 1

Author Comment

by:Garfy
ID: 6946441
thank you thank you thank you.  You are a marvel.  Is there a function that does the reverse??????
0
 
LVL 6

Expert Comment

by:JohnDesautels
ID: 13872773
I hunted for a way to concatenate a binary field with text.

Master.dbo.fn_varbintohexstr worked for me!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 27
sql server concatenate fields 10 35
optimize stored procedure 6 27
Present Absent from working date rage 11 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question