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.

asked 23 Feb '11, 13:54

Sergio's gravatar image

Sergio
460151820
accept rate: 0%

edited 20 Sep '11, 17:30

Volker%20Barth's gravatar image

Volker Barth
31.4k316458676


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
permanent link

answered 23 Feb '11, 14:17

Breck%20Carter's gravatar image

Breck Carter
26.9k440613886
accept rate: 21%

Thanks for the fast response!

(23 Feb '11, 14:54) Sergio

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
permanent link

answered 24 Feb '11, 14:58

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×14
×4

question asked: 23 Feb '11, 13:54

question was seen: 1,932 times

last updated: 20 Sep '11, 17:30