Converting a Hex String to a Varbinary or vice versa

I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?

? For the opposite, the easiest way is to use the built-in (but undocumented) function, fn_varbintohexstr: DECLARE @.x BINARY(16) SET @.x = NEWID() SELECT master.dbo.fn_varbintohexstr(@.x) ... Going the other way around is not quite so straightforward. I usually use dynamic SQL: DECLARE @.y CHAR(34) SET @.y = '0x499104dc92dd27499da9ad8f56dcb437' DECLARE @.x BINARY(16) DECLARE @.sql NVARCHAR(200) SET @.sql = 'SELECT @.x = ' + @.y EXEC sp_executesql @.sql, N'@.x BINARY(16) OUTPUT', @.x OUTPUT SELECT @.x -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Orphaned Zombie@.discussions.microsoft.com> wrote in message news:76360e15-52e8-499f-87b5-44f9eb1f0579@.discussions.microsoft.com... I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?|||

Please take a look at the links below for some options:

Integer value to hex

http://www.umachandar.com/technical/SQL6x70Scripts/Main89.htm

Hex to integer

http://www.umachandar.com/technical/SQL70Scripts/Main29.htm

If you don't have a table of numbers (which the 2nd link uses), you can use code like below:

declare @.t varchar (8)
select @.t = 'FFFFFFFF'
select sum( (charindex( lower( substring( hexstr , number + 1, 1 ) ), hexchars ) - 1) * convert( decimal( 28 , 0 ) , power( 16 , number ) )
)
from
(
select reverse( @.t ) as hexstr, '0123456789abcdef' as hexchars
) as h1
cross join
(select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7) as n(number)
go

You can extend this to generate 64-bit integer/bigint values also.