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?
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?
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
DECLARE @MyBinary VARBINARY(11)
SELECT @MyBinary = CONVERT(VARBINARY, 'Eureka!!!', 1)
DECLARE @Str VarChar(20)
SET @Str=CAST(@MyBinary as VARCHAR(20) )
SELECT @Str
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? :)
ASKER
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.
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
ASKER
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.
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_varbintohex str(@b )
print @str
declare @b binary(4), @str varchar(255)
select @b = 3455643
set @str =master.dbo.fn_varbintohex
print @str
Have you tried it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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! :)
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! :)
select @b = 3455643
exec master..xp_varbintohexstr @b, @str out
print @str