varbinary(float) to int

  • Hi

    I have float values expressed within a varbinary string - I want to convert the float values to numerics.  Any ideas on how to make this work?

    declare @f float

    declare @v varbinary(8)

    set @f = 100

    select @v = (select cast (@f as varbinary(8)))

    select @v

    select cast(floor(cast(@f as decimal(10,0)) ) as int)

    select cast(floor(cast(@v as decimal(10,0)) ) as int)

    The last select fails.

    Thanks.

  • The problem is that it's not storing the string representation of @f in @v. It's actually converting the float directly into @v. If you convert @f to a string (using varchar), then store it in varbinary, it's a piece of cake. Popping it directly into @v on the other hand, leaves you with the hex representation of "@Y", and at the moment, I have no idea how to get that back to the value you want without studying their floating point encoding method.

    Does that make sense?

     

  • Hi

    Yes - I've already jumped though various hoops & agree that the main issue is with the representation of the [float].  Unfortunately I can't change the input data format.  If anything else comes to mind please let me know.

    Thanks.

  • I know the basics of how it's stored, but I don't remember the specifics, and I'm pretty sure that for every rule there were exceptions. I'd advise a search for "sql server" float mantissa exponent and start studying since you have no control over the data format. This might be a good place to get started.

  • Well, for sure, SQL Server isn't following the IEEE-754 standard.  If you visit the link David pointed out (nice link, by the way... setup a spreadsheet to do all that), the example they give is for .15625 and they lay out the floating point thusly...

    SIGN

    Exponent

    Mantisa

    0

    0

    1

    1

    1

    1

    1

    0

    0

    0

    1

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

     

    If you slice that into "nibbles" starting from the left, you get...

    3

    E

    2

    SIGN

    Exponent

    Mantisa

    0

    0

    1

    1

    1

    1

    1

    0

    0

    0

    1

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

    0

     

    If you run David R's code with the .15625 number from the example, you get the following...

    declare @f float

    declare @v varbinary(8)

    set @f = .15625

    select @v = (select cast (@f as binary(8)))

    select @v

    ------------------

    0x3FC4000000000000

    (1 row(s) affected)

    Obviously, 3E20 is not equal to 3FC4 so something else is going on... haven't tried it with the 11 bit exponent yet...

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

  • It does use an 11 bit mantisa... got the .15626 example and the even numbers through 10 (thinking thats a hint!) to work...  THIS is a bugger!  It would be so much easier to simply strangle the bugger that did this to you!!

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

  • By the way, I'm assuming this is an app you are having to deal with, so could you provide us with an actual value that the app displays, as well as the string stored for that particular value in the database? While your example does the direct conversion, isn't it possible that they are doing it the way that I said would be a snap? You could actually make an argument for using varbinary for that reason (a numeric with 1000 places to the right of the decimal with no loss, for example, although varchar would work just as well), whereas converting the actual float directly doesn't pass the smell test to me.

  • Oh, hell yes!  Got the spreadsheet doing it right... trying to convert it to an SQL function...

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

  • You can CAST a BINARY(8) to BIGINT, if that helps out with bit checking later...

     


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

  • 'Zactly where I'm headed...

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

  • All right... here ya go... as I always say (in a "Yogi" Berra fashion), "Once ya figure it out, it's easy"

    I haven't tried it on every bloody number in the world, but I've included a couple of test harnesses in the header if YOU want to

    As a side note, if you're careful about how you type the replacements for the @Num variables (they gotta be big FLOAT's for accuracy), you could turn this into a View 'cause it's all done in a single nested SELECT.

    drop function fConvertBinFloatToFloat

    GO

     CREATE FUNCTION dbo.fConvertBinFloatToFloat

    /**********************************************************************************************************************

     Purpose:

     This function accepts a numeric value that was converted to FLOAT and saved as a 64 bit VARBINARY(8) or BINARY(8)

     datatype and converts it back to FLOAT.  This function uses an 11 bit Exponent.

     References:

     http://en.wikipedia.org/wiki/IEEE_754 (Special thanks to David McFarland at SQLServerCentral.com for the great link!)

     Notes:

     1. Requires a "Tally" table consisting of a single column of well indexed numbers of 1 to >64 be present in the

        same database.

     2. The number "1023" is the required exponent bias (e-bias) for 11 bit Exponents

     3. Number format of the BinFloat is as follows...

     4. Execution rate is a bit slow at only 1,800 rows per second

          SIGN BIT|<-----EXPONENT---->|<-----Mantisa---->>>> to bit 64

                 S|E E E E E E E E E E|M M M M M M M M M

     Power of 2  0|1 0 0 0 0 0 0 0 0 0|0 0 0 0 0 0 0 0 1 ... translates to 1/(2^(Power of 2))during calculation

                 1|0 9 8 7 6 5 4 3 2 1|2 3 4 5 6 7 8 9 0 ... ETC

                  |                   |

     BitNum      0|0 0 0 0 0 0 0 0 1 1|1 1 1 1 1 1 1 1 2

                 1|2 3 4 5 6 7 8 9 0 1|2 3 4 5 6 7 8 9 0 ... etc

     Example Usage:

     SELECT dbo.fConvertBinFloatToFloat(0xC05DA80000000000) will return -118.625

     Example Test Harnesses:

     --===== Test Harness 1 (100% manual)

     DECLARE @SomeNumber FLOAT

         SET @SomeNumber = -118.625 --<<<<< Change this number

      SELECT @SomeNumber AS OriginalVal,

             CAST(@SomeNumber AS BINARY(8)) AS BinaryFloatValue,

             dbo.fConvertBinFloatToFloat(CAST(@SomeNumber AS BINARY(8))) AS ConvertedValue

     

     --===== Test Harness 2 (auto gen/verify)

      SELECT CAST(N AS FLOAT(53))/100 AS OriginalVal,

             CAST(CAST(N AS FLOAT(53))/100 AS BINARY(8)) AS BinaryFloatValue,

             dbo.fConvertBinFloatToFloat(CAST(CAST(N AS FLOAT(53))/100 AS BINARY(8))) AS ConvertedValue,

             CAST(N AS FLOAT(53))/100

           - dbo.fConvertBinFloatToFloat(CAST(CAST(N AS FLOAT(53))/100 AS BINARY(8))) AS Error

        FROM Tally

     Revision History:

     Rev 00 - 04/08/2007 - Jeff Moden - Initial Creation and unit test

    **********************************************************************************************************************/

            (@BinFloat BINARY(8))

            RETURNS FLOAT

         AS

      BEGIN

    --===== Declare local variables to replace short constants with long FLOATs for accuracy

    DECLARE @Num1  FLOAT(53)

    DECLARE @Num2  FLOAT(53)

    DECLARE @Num12 FLOAT(53)

    DECLARE @Num64 FLOAT(53)

        SET @Num1  = 1

        SET @Num2  = 2

        SET @Num12 = 12

        SET @Num64 = 64

     RETURN (

     SELECT POWER(@Num2,em.Exponent)*em.Mantisa*SIGN(CAST(@BinFloat AS BIGINT))

       FROM

            (   

             SELECT SUM(CASE WHEN b.Part = 'Exponent' AND b.BitIsSet = 1 THEN b.BitValue ELSE 0 END)-1023 AS Exponent,

                    SUM(CASE WHEN b.Part = 'Mantisa'  AND b.BitIsSet = 1 THEN @Num1/b.BitValue ELSE 0 END)+@Num1 AS Mantisa

               FROM

                (

                SELECT 64-n+1 AS BITNo, --Just for troubleshooting... starts at 2, ends with 64, sign bit left out

                        SIGN(@BinFloat & CAST(POWER(@Num2,CAST(N-1 AS FLOAT(53))) AS BIGINT)) AS BitIsSet,

                        CASE WHEN (64-n+1) <=12

                             THEN POWER(@Num2,@Num12-(@Num64-n+@Num1))                    --Is part of Exponent

                             ELSE CAST(POWER(@Num2,(@Num64-n+@Num1)-@Num12) AS FLOAT(53)) --Is part of Mantisa

                        END AS BitValue,

                        CASE WHEN 64-n+1 <=12

                             THEN 'Exponent'  --Exponent in bits 2 thru 12 with values 2, 4, 8, 16, etc from right to left

                             ELSE 'Mantisa'   --Mantisa in bits 13+ with values 2, 4, 8, 16, etc from left to right

                        END AS Part

                FROM TALLY

                WHERE N<=63

                ) b

            )em

            )

    END

    That was a lot of fun ... hadn't done that kind of binary stuff in a long time.  Lemme know how it works out for ya, huh?

    Oh yeah... almost forgot... if you don't already have a "Tally" table (they're good for LOTS of different things), here's how you make one...

    --===== Create and populate the Tally table on the fly

     SELECT TOP 11000 --equates to more than 30 years of dates

            IDENTITY(INT,1,1) AS N

       INTO dbo.Tally

       FROM Master.dbo.SysColumns sc1,

            Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

      ALTER TABLE dbo.Tally

            ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

    --===== Allow the general public to use it

      GRANT SELECT ON dbo.Tally TO PUBLIC

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

  • ...And, now that we've done all of that... I'll bet the damned thing will import using BCP with one of the "native" datatype identifiers... I'll have to try that out sometime. 

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

  • Nice job, Jeff. Did you notice any "lossy" numbers at all, and if not, did you try fun things like putting 1/3 into the varbinary for testing? The main reason that I'm asking is that the original test scripts we were looking at had FLOOR in them, and if you noticed the typical floating point problems, ROUND might be better. I'd hate to see 100 returned as 99.99999... and have it end up returning 99.

  • Heh... of course if a "lossy" number was stored to begin with, the function will return that same "lossy" number which is what the requirement was... its a FLOAT, not a DECIMAL   That's why I can't believe anyone would store data as a FLOAT unless it's an 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

  • Jeff - That was/is truely awesome ().  I've just used your function to select out a set of decimals from the varbinary string & it works like a dream.  Very well done!! 

    Well - enough of my babbling admiration & thanks to all for their repsonses.

    PS As to the (a) use of floats & (b) packing these & other data into a image field - well, I'm sure that it seemed like a  good idea at the time.

     

     

Viewing 15 posts - 1 through 15 (of 40 total)

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