TSQL – make a literal float value

I understand the host of issues in comparing floats, and lament their use in this case – but I’m not the table author and have only a small hurdle to climb…

Someone has decided to use floats as you’d expect GUIDs to be used. I need to retrieve all the records with a specific float value.

sp_help MyTable

-- Column_name  Type    Computed    Length  Prec
-- RandomGrouping   float   no  8   53

Here’s my naive attempt:

--yields no results
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping = 0.867153569942739

And here’s an approximately working attempt:

--yields 2 records
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.867153569942739 - 0.00000001
      AND 0.867153569942739 + 0.00000001

--  0.867153569942739
--  0.867153569942739

In my naive attempt, is that literal a floating point literal? Or is it really a decimal literal that gets converted later?

If my literal is not a floating point literal, what is the syntax for making a floating point literal?

EDIT: Another possibility has occurred to me… it may be that a more precise number than is displayed is stored in this column. It may be impossible to create a literal that represents this number. I will accept answers that demonstrate that this is the case.

EDIT: response to DVK.

TSQL is MSSQLServer’s dialect of SQL.

This script works, and so equality can be performed deterministically between float types:

DECLARE @X float
SELECT top 1 @X = RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.839110948199148 - 0.000000000001
  AND 0.839110948199148 + 0.000000000001
  --yields two records
SELECT *
FROM MyTable
WHERE RandomGrouping = @X

I said “approximately” because that method tests for a range. With that method I could get values that are not equal to my intended value.

The linked article doesn’t apply because I’m not (intentionally) trying to straddle the world boundaries between decimal and float. I’m trying to work with only floats. This isn’t about the non-convertibility of decimals to floats.

Response to Zinglon:

A literal value that can find my records, thanks.

DECLARE @Y binary(8)
SET @Y = 0x3FEAD9FF34076378

SELECT *
FROM MyTable
WHERE convert(binary(8), RandomGrouping) = @Y

Answer

It is possible that the values are being truncated on display. I’m assuming the column doesn’t have a unique constraint on it, otherwise the question would be moot. On my setup, SSMS truncates the more precise value in this script.

create table flt ( f float not null primary key )
insert into flt
select 0.111111111111111
union all
select 0.1111111111111111
select f, cast(f as binary(8)) from flt

Similarly, if these values are distinct you can cast them to binary(8) and identify them based on that value, like this:

select f from flt
where cast(f as binary(8)) = 0x3FBC71C71C71C71C

Advertisement

Answer