Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    8

    Unanswered: How can i convert binary(16) to Integer or numeric?

    HI!!!

    Im trying to convert 0x00085180F0A2D511B69600508BE96424 to Integer or numeric format.

    I just tried, At to many forms and combinations of that query
    Help me !!!

    SELECT CAST(CAST(CAST("field name " AS nvarchar) AS varbinary) AS float)

    select cast(cast("field name " as varbinary)as integer)

    select convert(int," field name") from FILE

    select convert(varchar," field name") from FILE



    The only answer that I have is
    -1947638748 or ‘ ‘ or

    And if I try with to many rows of the field at the same format,
    It Answer me the same: -1947638748 for all the rows.

    Thank`s for all

  2. #2
    Join Date
    Dec 2003
    Posts
    61

    convert binary to integer

    create table #t1(f1 binary(16))

    select *
    from #t1

    insert into #t1 values(convert(binary(16),'0x00085180F0A2D511B6960 0508BE96424'))

    select convert(integer, f1)
    from #t1

    --The result is 1093813301. I dont see -1947638748

    drop table #t1

  3. #3
    Join Date
    Feb 2006
    Posts
    8

    OK thanks

    THANK YOU!!!, forXLDB

    Just one more question please

    What’s the reason for the space in the middle of the expression?

    Before convert

    Original Expresión
    '0x00085180F0A2D511B69600508BE96424'

    Convert expresión
    '0x00085180F0A2D511B6960 0508BE96424'

  4. #4
    Join Date
    Feb 2006
    Posts
    8
    thank you...

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, but the actual answer is MUCH larger! You can't express that VARBINARY value as an integer, or even as a NUMERIC(38) which is the largest SQL Server will allow. The following code shows what I mean:
    Code:
    DECLARE
       @b		VARBINARY(16)		-- binary image to convert
    ,  @i		INT			-- Which byte we're working on
    ,  @m		FLOAT			-- Multiplier for this byte
    ,  @a		FLOAT			-- Accumulator
    
    SET @b = 0x00085180F0A2D511B69600508BE96424 
    SELECT @i = DataLength(@b), @m = 1, @a = 0	-- Start with lowest order byte
    
    WHILE 0 < @i					-- While bytes left to process
       BEGIN
    --    SELECT @a, @m, @i, SubString(@b, @i, 1)	-- Show your work
          SELECT @a = @a + (@m * CAST(SubString(@b, @i, 1) AS INT))
          SELECT @m = 256 * @m, @i = @i - 1		-- Prepare for next byte
       END
    
    SELECT @m					-- Show results
    -PatP

  6. #6
    Join Date
    Feb 2006
    Posts
    8

    OK, it works

    Thanks Pat Phelan

    I like that explication step by step.
    It really please me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •