Post reply

varbinary(float) to int

  • November 21, 2008 at 4:05 pm

    #901918

    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"

  • July 14, 2011 at 6:37 am

    #1352760

    hi Jeff and Peter,

    Thank you very much for a super solution.

    Best regards,

    Henrik

  • August 23, 2011 at 7:39 am

    #1371990

    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

  • August 23, 2011 at 4:30 pm

    #1372298

    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)

  • August 24, 2011 at 1:21 am

    #1372378

    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

  • August 24, 2011 at 2:10 am

    #1372395

    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"

  • August 24, 2011 at 4:46 am

    #1372457

    hi Peter,

    That was a smart use of NULLIF.

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

    Best regards,

    Henrik

  • August 28, 2011 at 6:52 pm

    #1374359

    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)

  • August 29, 2011 at 12:25 am

    #1374379

    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

  • August 29, 2011 at 8:30 am

    #1374504

    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)

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

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