Converting hex values to int.

Question

I have to convert values like 0x01A4 to integer and I just can not get it right!  

Select Convert(int, 0x01A4)  works OK but is not useful.

 

But

Declare @hex varchar(20)

Select Convert(int, @b)   yields:

Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the varchar value '0x01A4' to a column of data type int.

 

What's the trick??

Answer 1

Did you try declaring the variable/parameter as int and directly assigning the hex value?

 

Example:

 

DECLARE @hex int
SET @hex = 0x01A4
SELECT @hex

 

-----------
420

Answer 2

Can you make your example work when passing-in the hex value as a string?

Answer 3

Sure. This is one of the situations where the SQL_Variant datatype comes in handy.

 

Code Snippet

DECLARE @MyParameter sql_variant

SET @MyParameter = 0x01A4

DECLARE @hex int

SET @hex = cast( @MyParameter AS int )

SELECT MyNumber = @hex

 

MyNumber   
-----------
420

 

 

 

Answer 4

There is no easy way to convert from a hex value in string format to int or varbinary. If you are using SQL Server 2005 then you can use the approach below. Otherwise you have to write your own UDF that does the conversion.

 

Code Snippet

declare @hexstring varchar(max);
set @hexstring = 'abcedf012439';
select cast('' as xml).value('xs:hexBinary(
substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
  from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)

 

 

The link below contains some solutions for older versions of SQL Server.

 

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

 

Answer 5

As luck would have it, I am using 2000. . . . and I must convert from varchar (not sql_variant). I guess I am out of luck.  Thanks for your help! 

Answer 6

Check the link that I posted. It has some solutions for SQL2000.

Answer 7

This solution is totally groovy.