varbinary(float) to int

  • 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"

  • hi Jeff and Peter,

    Thank you very much for a super solution.

    Best regards,

    Henrik

  • 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

    END

    What do you think?

    Best regards,

    Henrik Staun Poulsen

    Stovi Software

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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"

  • hi Peter,

    That was a smart use of NULLIF.

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

    Best regards,

    Henrik

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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