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-2 varbinary(8)
set @f = 100
select @v-2 = (select cast (@f as varbinary(8)))
select @v-2
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-2 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-2 varbinary(8)
set @f = .15625
select @v-2 = (select cast (@f as binary(8)))
select @v-2
------------------
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) -
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) -
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) -
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) -
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) -
...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) -
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) -
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 39 total)
You must be logged in to reply to this topic. Login to reply