Answered by:
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.Friday, August 10, 2007 5:55 PM
Answers
-
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 Snippetdeclare @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)Friday, August 10, 2007 7:32 PM
All replies
-
Did you try declaring the variable/parameter as int and directly assigning the hex value?
Example:
DECLARE @hex int
SET @hex = 0x01A4
SELECT @hex
420Friday, August 10, 2007 6:12 PM -
Can you make your example work when passing-in the hex value as a string?
Friday, August 10, 2007 6:35 PM -
Sure. This is one of the situations where the SQL_Variant datatype comes in handy.
Code SnippetDECLARE @MyParameter sql_variant
SET @MyParameter = 0x01A4
DECLARE @hex intSET @hex = cast( @MyParameter AS int )
SELECT MyNumber = @hex
MyNumber
-----------
420Friday, August 10, 2007 6:50 PM -
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 Snippetdeclare @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)Friday, August 10, 2007 7:32 PM -
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!
Friday, August 10, 2007 8:23 PM -
Check the link that I posted. It has some solutions for SQL2000.Friday, August 10, 2007 9:34 PM
-
This solution is totally groovy.Saturday, March 26, 2011 4:38 AM