Getting Max Decimal places in a float SQL Server 2012

Question

I understand float is stored as binary and is a representation in SQL Server. However, I need the max number of decimal places that are represented in a float column in my table. I found this link which explains how to get the number of decimal places per row. However, I can't figure out how to get the max for that result. In essence this is what I want to do.

SELECT MAX
       (Decimals = CASE Charindex('.', [QUANTITY])
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), [QUANTITY], 128)
                     ) AS FLOAT) AS BIGINT))
                  END)
FROM   [schema].[table]

This gives me a syntax error after the END. I have tried a sub query and can't seem to get the syntax correct either.

score 1 · Answer 1 · answered Mar 26 '15 at 14:12

You can use convert() function with style = 128, but you can read that this is [Included for legacy reasons and might be deprecated in a future release]. Another way would be to use str() function:

declare @temp_data table (quantity float)

insert into @temp_data
select 1.55552 union all
select 0.545365788

;with cte as (
    select convert(varchar(max), quantity, 128) as quantity from @temp_data
)
select 
    max(
        case
            when charindex('.', quantity) = 0 then 0
            else len(quantity) - charindex('.', quantity)
        end
    )
from cte

;with cte as (
    select rtrim(ltrim(str(quantity, 100, 100))) as quantity from @temp_data
)
select
    max(
        case
            when charindex('.', quantity) = 0 then 0
            else len(cast(cast(reverse(quantity) as float) as bigint))
        end
    )
from cte

score 0 · Answer 2 · answered Mar 26 '15 at 14:02

It looks like this is the answer...

SELECT MAX
       (CASE Charindex('.', [QUANTITY])
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), [QUANTITY], 128)
                     ) AS FLOAT) AS BIGINT))
                  END)
FROM   [schema].[table]

Sorry Everyone. I thought I tried this in my many derivations but apparently not or I had a typo. If somebody can explain why putting the "Decimals =" in makes it invalid it would help me understand why.

If you were trying to name the result column, then the name should come after the clause: `SELECT MAX(CASE... WHEN... END) AS Decimals FROM...` — Tanner, Mar 26 '15 at 14:05

score 0 · Answer 3 · answered Dec 11 '20 at 17:29

SELECT   MAX ( CASE Charindex('.', <ColumnName>)
                  WHEN 0
                     THEN
                        0
                     ELSE
                        LEN( CAST( CAST( REVERSE( CONVERT( varchar(50), <ColumnName>, 128 ) ) AS float) AS bigint))
               END
             ) AS [FloatPrecision]
       , <ColumnName>
   FROM <schema>.<tablename>
   GROUP BY <ColumnName>
   ORDER BY [FloatPrecision] DESC;

This solution gives you the Float Precisions, with the actual values from the most precise, to the least. In my case, I had some cost values out to 11 decimal places, which didn't make a lot of sense.

Getting Max Decimal places in a float SQL Server 2012

3 Answers3