Convert a hexadecimal string to BIGINT on SA10

[Former Member]

The title says it all. HEXTOINT can only convert integers up to 32 bit. I need to convert a 48 bit-long number from hex to decimal and viceversa.

[Breck_Carter · Accepted solution]

BEGIN
DECLARE @hex VARCHAR ( 100 );
DECLARE @bigint BIGINT;
SET @hex = '7048860DDF79';
EXECUTE IMMEDIATE STRING ( 'SET @bigint = CAST ( 0x', @hex, ' AS BIGINT )' );
SELECT @bigint;
END;

@bigint
123456789012345

[Former Member · Accepted solution]

Thanks for the fast response!

[Former Member]

Another option, inspired by Breck's solution:

   create or replace function hextobigint( @in varchar(16) )
    returns unsigned bigint
    begin
      declare @result unsigned bigint;
      set @result = (select cast(v1 as unsigned bigint) 
          from openstring(value '0x' || @in) with (v1 binary(8)) 
                   option(hexadecimal on) as x);
      return @result;
    end
    go
    select hextobigint( '7048860DDF79')
    go