varbinary(float) to int

Question

Post reply

varbinary(float) to int

Viewing 10 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Really well done!

Do you have the time to test the speed of these simplified inline functions too?

CREATE FUNCTION dbo.fnBinaryFloat2Float

(

@BinaryFloat BINARY(8)

)

RETURNS FLOAT

AS

BEGIN

RETURNSIGN(CAST(@BinaryFloat AS BIGINT))

* (1.0 + (CAST(@BinaryFloat AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52))

* POWER(CAST(2 AS FLOAT), (CAST(@BinaryFloat AS BIGINT) & 0x7ff0000000000000) / 0x0010000000000000 - 1023)

END

CREATE FUNCTION dbo.fnBinaryReal2Real

(

@BinaryFloat BINARY(4)

)

RETURNS REAL

AS

BEGIN

RETURNSIGN(CAST(@BinaryFloat AS INT))

* (1.0 + (CAST(@BinaryFloat AS INT) & 0x007FFFFF) * POWER(CAST(2 AS REAL), -23))

* POWER(CAST(2 AS REAL), (CAST(@BinaryFloat AS INT) & 0x7f800000) / 0x00800000 - 127)

END

N 56°04'39.16"
E 12°55'05.25"

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 2

hi Jeff and Peter,

Thank you very much for a super solution.

Best regards,

Henrik

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 3

I've started using your function, but...

SELECT utl.fnBinaryReal2Real(0x43F8E354) -- 497,776

SELECT utl.fnBinaryReal2Real(0xFFFFF2D7) -- overflows

Yes, I think it is my source that is sending rubbish, but I would like the function to survive.

Here is a way of returning NULL (instead of NaN) when I get rubish.

CREATE FUNCTION utl.fnBinaryReal2Real

(

@BinaryFloat BINARY(4)

)

RETURNS REAL

AS

BEGIN

RETURNCASE WHEN (CAST(@BinaryFloat AS INT) & 0x7f800000)/ 0x00800000 = 255 THEN NULL

ELSE

SIGN(CAST(@BinaryFloat AS INT))

* (1.0 + (CAST(@BinaryFloat AS INT) & 0x007FFFFF) * POWER(CAST(2 AS REAL), -23))

* POWER(CAST(2 AS REAL), (CAST(@BinaryFloat AS INT) & 0x7f800000) / 0x00800000 - 127)

END

What do you think?

Best regards,

Henrik Staun Poulsen

Stovi Software

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 4

henrik staun poulsen (8/23/2011)
What do you think?

I don't know why anyone would think that 0xFFFFF2D7. It's simply a negative integer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 5

Hi Jeff,

Well, yes that might be true. But my meta data says that it is a float. So my transform job crashed.

With my improvement, the code continues to run on the valid data, and return NULL on the invalid data.

I've also been reading http://en.wikipedia.org/wiki/Single_precision and http://en.wikipedia.org/wiki/NaN

and it states that an exponent of FFh is Not a Number (NaN)

So is my improvement not entirely wrong?

Best regards,

Henrik

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

You can somewhat simplify the NaN check by adding NULLIF to the formula for not having to do the & and / operation twice.

ALTER FUNCTIONdbo.fnBinaryReal2Real

(

@BinaryFloat BINARY(4)

)

RETURNS REAL

AS

BEGIN

RETURNSIGN(CAST(@BinaryFloat AS INT))

* (1.0 + (CAST(@BinaryFloat AS INT) & 0x007FFFFF) * POWER(CAST(2 AS REAL), -23))

* POWER(CAST(2 AS REAL), NULLIF((CAST(@BinaryFloat AS INT) & 0x7f800000) / 0x00800000, 255) - 127)

END

N 56°04'39.16"
E 12°55'05.25"

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 7

hi Peter,

That was a smart use of NULLIF.

NULLIF(xx, 255) is just what is needed.

Best regards,

Henrik

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 8

henrik staun poulsen (8/24/2011)
Hi Jeff,
Well, yes that might be true. But my meta data says that it is a float. So my transform job crashed.
With my improvement, the code continues to run on the valid data, and return NULL on the invalid data.
I've also been reading http://en.wikipedia.org/wiki/Single_precision and http://en.wikipedia.org/wiki/NaN
and it states that an exponent of FFh is Not a Number (NaN)
So is my improvement not entirely wrong?
Best regards,
Henrik

Wouldn't it be easier to change the meta-data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henrik Staun Poulsen SSCertifiable Points: 6592 More actions · Answer 9

Hi Jeff,

It is not question of doing either this or that; I need to both fix the code so that it does not crash, then when I know what signal that is missing, then fix the meta data for that signal.

Best regards,

Henrik

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 10

That's kind of what I'm suggesting, Henrik.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

varbinary(float) to int

Cookies on SQLServerCentral