Link to home
Start Free TrialLog in
Avatar of Ruan1618
Ruan1618

asked on

SQL VarBinary To Text

Hi,

I have the following problem. I have a VarBinary variable in SQL Server 2005 that I need as a VarChar type.

DECLARE @MyBinary VARBINARY(11)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1)

If I now select @MyBinary I get the following value 0x457572656B61212121 which is exactly what I want. The only problem I am having is I need this value as a VarChar type and not as a VarBinary. I cannot do a CONVERT because then the value will change.

This means that I need to be able to get this value into a VarChar so that it looks as follows.

DECLARE @Str VarChar(20)
@Str = "0x457572656B61212121"

Can anyone help me with this?
Avatar of nkhelashvili
nkhelashvili

declare @b binary(4), @str varchar(255)
select @b = 3455643
exec master..xp_varbintohexstr @b, @str out
print @str
use CAST

DECLARE @MyBinary VARBINARY(11)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1)
DECLARE @Str VarChar(20)
SET @Str=CAST(@MyBinary as VARCHAR(20) )
SELECT @Str
Avatar of Ruan1618

ASKER

There is just one problem with this. master..xp_varbintohexstr is a SQL2000 Stores Proc. If I use this in SQL2005 I have to use sys.fn_varbintohexstr. The problem is that we use both SQL2000 and SQL2005 and therefore the code that I write has to be compatible in both. We will also be supporting SQL2008 in the future... So this leave me with another little problem! :-S
I realized it and offered you second solution, have you checked it?  :)
Yes, I did see it thanks, but the second solution does not solve my problem. When I do it like that I get "Eureka!!!" as a VarChar type back where I would actually like to see "0x457572656B61212121" as a VarChar type.

The function sys.fn_varbintohexstr give me exactly what I want, but it is not compatible between SQL2000 and SQL2008.
I don`t know where you tested, but when I am executing following sql, it displays "Eureka!!!"
DECLARE @MyBinary VARBINARY(11)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1) 
DECLARE @Str VarChar(20)
SET @Str=CAST(@MyBinary as VARCHAR(20) )
SELECT @Str

Open in new window

When I execute this SQL it returns 'Eureka!!!' as you said. But that is not the result that I need to see.

DECLARE @MyBinary VARBINARY(11)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1)
DECLARE @Str VarChar(20)
SET @Str=CAST(@MyBinary as VARCHAR(20) )
SELECT @Str AS [RESULT]

~~~~~~~~~~~~~~~~~~~~~~
RESULT
--------------------
Eureka!!!
~~~~~~~~~~~~~~~~~~~~~~

The result I would like to see is exactly the one returned by the following SQL:

DECLARE @MyBinary VARBINARY(11)
DECLARE @Str VarChar(20)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1)
exec master..xp_varbintohexstr @MyBinary, @str out
SELECT @Str AS [RESULT]

~~~~~~~~~~~~~~~~~~~~~~
RESULT
--------------------
0x457572656B61212121
~~~~~~~~~~~~~~~~~~~~~~

So this means that master..xp_varbintohexstr give me the correct result, but the problem is that it is not compatible with SQL2005. I need something that is compatible with SQL2000 and SQL2005.


here it is:

declare @b binary(4), @str varchar(255)
select @b = 3455643
 set  @str =master.dbo.fn_varbintohexstr(@b )
print @str

Have you tried it?
ASKER CERTIFIED SOLUTION
Avatar of sampipes
sampipes

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

Sorry, had some other things I had to attend to, but im back again! :) I LOVE the last answer, it work perfectly for me! Thank you very much for all the help! :)