none
Converting hex values to int. RRS feed

  • 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??
    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 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.
     
     
    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

     

    -----------
    420
    Friday, August 10, 2007 6:12 PM
    Moderator
  • 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 Snippet

    DECLARE @MyParameter sql_variant

    SET @MyParameter = 0x01A4


    DECLARE @hex int

    SET @hex = cast( @MyParameter AS int )

    SELECT MyNumber = @hex

     

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

     

     

     

    Friday, August 10, 2007 6:50 PM
    Moderator
  • 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.
     
     
    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