TSQL to identify long Float values.

Question

I'm dealing with a legacy system where I need to identify some bad records based on a column with a data type of Float.

Good records have a value of...

1
2
1.01
2.01

Bad records are anything such as..

1.009999999999999
2.003423785643000
3.009999990463260

I've tried a number of select statements where I Convert to Decimal and cast to a varchar and use the LEN() function but this don't seem to work as the good records that are 1.01 become 1.0100000000000000

--Edit I'm a little closer now as I have discovered I can do (Weight * 100) and all of the good records become whole number values such as 101,201,265,301,500, etc... and bad ones such as 2.00999999046326 become 200.999999046326

Answer 1

This works on my SQL Server 2005 DB:

select len(cast(cast(1.01 as float) as varchar))

Result:

4

In fact, it even lets me skip the explicit varchar cast if I want to:

select len(cast(1.011 as float))

Result:

5

---

Update: First of all, I still needed the cast to varchar. Thinking otherwise was wrong. That said, I had this all working using strings and was about to post how. Then you I your update on mulitpling by 100 and realized that was the way to go. So here's my code for testing both ways:

declare @test table ( c float)
insert into @test 
 select * from 
  ( select 14.0100002288818 as c union
    select 1.01 union
    select 2.00999999046326 union
    select 14.01
   ) t

select c,
  case when c = cast(cast(c as varchar) as float) AND LEN(cast(c as varchar))<=5 then 1 else 0 end,
  case when c * 100 = floor(c * 100) then 1 else 0 end
from @test

Answer 2

something like this, maybe? (adjust the precision/scale in the where clause, of course)

select val from mytable WHERE CONVERT(decimal(5,2), val) <> val

Answer 3

You could do something like this:

SELECT * 
FROM YourTable
WHERE CAST(YourFloatField AS DECIMAL(15,2)) <> YourFloatField

I'm assuming that anything "bad" has more than 2 decimal places given.

Answer 4

This really will become a pain in the neck because floats are an imprecise datatype and you will get implicit conversions when casting.

it also depends where you run something like the following

select convert(float,1.33)

in query analyzer the output is 1.3300000000000001 in SSMS the output is 1.33

when you convert to decimal you need to specify scale and precision

so if you do

select convert(decimal(10,6),convert(float,1.33))

you get this 1.330000 because you specified a scale of 6

you could do something like this where after converting to decimal you drop the trailing 0s

select replace(rtrim(replace(convert(varchar(30),
    (convert(decimal(10,6),convert(float,1.33)))),'0',' ')),' ','0')

for a value of 3.00999999046326 you need a scale of at least 14

select replace(rtrim(replace(convert(varchar(30),
    (convert(decimal(30,14),convert(float,3.00999999046326)))),'0',' ')),' ','0')

Answer 5

Have you thought about using CLR integration and using .net to handle the validation see this link Basics of Using a .NET Assembly in MS SQL - User Functions

basically you use .net methods as a user defined function to do the validation; .NET is better at working with numbers.

Answer 6

Run this:

DECLARE @d FLOAT;
SET @d = 1.23;
SELECT ABS(CAST(@d AS DECIMAL(10,2)) - CAST(@d AS DECIMAL(15,8)));
SET @d = 1.230000098;
SELECT ABS(CAST(@d AS DECIMAL(10,2)) - CAST(@d AS DECIMAL(15,8)));

Use some threshold such as:

ABS(CAST(@d AS DECIMAL(10,2)) - CAST(@d AS DECIMAL(15,8)))<0.00001