Convert hexadecimal to varchar

Hi all,
I need to convert a value in hexadecimal to varchar, and this does not
seem to be as easy as
DECLARE @.hex varbinary
DECLARE @.str varchar
SET @.str = 'test'
--PRINT @.str
SET @.hex = 0x0000000000000001FFFF07D600002710
SET @.str = CAST(@.hex as varchar)
Any ideas? Has anyone tried this before? The reason I need this in
varchar is because I'm going to have to run a stored procedure based on
the hexadecimal value in a loop so I run the sp against all my
hexadecimal values. Any help would be greatly appreciated. Thanks.
-StuThere's the "undocumented" function fn_varbintohexstr that will do the
trick. If you prefer to go with a "documented" method, you can try
something like the following:
CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.hex VARCHAR(400)
SELECT @.hex = '0x'
DECLARE @.i INT
SELECT @.i = 1
WHILE (@.i < DATALENGTH(@.val))
BEGIN
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
& 240) / 16 + 1, 1)
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
& 15 + 1, 1)
SELECT @.i = @.i + 1
END
RETURN @.hex
END
GO
DECLARE @.hex VARBINARY(40)
SET @.hex = 0x0000000000000001FFFF07D600002710
SELECT dbo.udf_VarB2Hex (@.hex)
<stuart.karp@.gmail.com> wrote in message
news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> Hi all,
> I need to convert a value in hexadecimal to varchar, and this does not
> seem to be as easy as
> DECLARE @.hex varbinary
> DECLARE @.str varchar
> SET @.str = 'test'
> --PRINT @.str
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SET @.str = CAST(@.hex as varchar)
> Any ideas? Has anyone tried this before? The reason I need this in
> varchar is because I'm going to have to run a stored procedure based on
> the hexadecimal value in a loop so I run the sp against all my
> hexadecimal values. Any help would be greatly appreciated. Thanks.
> -Stu
>|||Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
procedure and use it as follows (for instance):
declare @.s varbinary(20), @.out varchar(20)
select @.s = first
from sysindexes
where id = 4
select @.s
exec master..sp_hexadecimal @.s, @.out output
select @.out
Linchi
"Mike C#" wrote:

> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i,
1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1
)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
>
>|||Hi Mike C#,
Thanks for the example. However you'll notice that for my example it's
2 more than a regular hexadecimal number. For your example it will
remove the 10 at the end of 0x0000000000000001FFFF07D600002710
This number is a GUID, and I need to run my stored procedure against
this exact number, which I want to turn into a string. Is there a good
way of modifying your code for this longer version that I have. I
would think that I could also remove 2 zeros at the beginning of my
GUID, but that's probably not the best idea, since some of my guids use
the beginning of this, ie something like
0xED2CD6A2E653DA4191B3A67272795AE8.
Anymore help to this? Thanks.
Mike C# wrote:[vbcol=seagreen]
> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i,
1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1
)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...|||Thanks Linchi.
That worked perfectly.
Appreciate it muchly.
Linchi Shea wrote:[vbcol=seagreen]
> Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
> procedure and use it as follows (for instance):
> declare @.s varbinary(20), @.out varchar(20)
> select @.s = first
> from sysindexes
> where id = 4
> select @.s
> exec master..sp_hexadecimal @.s, @.out output
> select @.out
> Linchi
> "Mike C#" wrote:
>|||Yeah, change the line
WHILE (@.i < DATALENGTH(@.val))
to
WHILE (@.i <= DATALENGTH(@.val))
<stuart.karp@.gmail.com> wrote in message
news:1156188225.257923.238440@.74g2000cwt.googlegroups.com...
> Hi Mike C#,
> Thanks for the example. However you'll notice that for my example it's
> 2 more than a regular hexadecimal number. For your example it will
> remove the 10 at the end of 0x0000000000000001FFFF07D600002710
> This number is a GUID, and I need to run my stored procedure against
> this exact number, which I want to turn into a string. Is there a good
> way of modifying your code for this longer version that I have. I
> would think that I could also remove 2 zeros at the beginning of my
> GUID, but that's probably not the best idea, since some of my guids use
> the beginning of this, ie something like
> 0xED2CD6A2E653DA4191B3A67272795AE8.
> Anymore help to this? Thanks.
> Mike C# wrote:
>