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

1. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2006
Posts
8
thank you...

5. Resident Curmudgeon
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. Registered User
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
•