November 21, 2008 at 4:05 pm
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
RETURN SIGN(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
RETURN 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
N 56°04'39.16"
E 12°55'05.25"
July 14, 2011 at 6:37 am
hi Jeff and Peter,
Thank you very much for a super solution.
Best regards,
Henrik
August 23, 2011 at 7:39 am
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
RETURN CASE 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
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
August 24, 2011 at 1:21 am
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
You can somewhat simplify the NaN check by adding NULLIF to the formula for not having to do the & and / operation twice.
ALTER FUNCTION dbo.fnBinaryReal2Real
(
@BinaryFloat BINARY(4)
)
RETURNS REAL
AS
BEGIN
RETURN SIGN(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
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
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
August 29, 2011 at 12:25 am
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
That's kind of what I'm suggesting, Henrik.
--Jeff Moden
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply