Search code examples
sqlsql-server-2008floating-pointdecimalscale

How do I count decimal places in SQL?


I have a column X which is full of floats with decimals places ranging from 0 (no decimals) to 6 (maximum). I can count on the fact that there are no floats with greater than 6 decimal places. Given that, how do I make a new column such that it tells me how many digits come after the decimal?

I have seen some threads suggesting that I use CAST to convert the float to a string, then parse the string to count the length of the string that comes after the decimal. Is this the best way to go?


Solution

  • You can use something like this:

    declare @v sql_variant
    
    set @v=0.1242311
    
    select SQL_VARIANT_PROPERTY(@v, 'Scale') as Scale
    

    This will return 7.


    I tried to make the above query work with a float column but couldn't get it working as expected. It only works with a sql_variant column as you can see here: http://sqlfiddle.com/#!6/5c62c/2

    So, I proceeded to find another way and building upon this answer, I got this:

    SELECT value,
    LEN(
        CAST(
             CAST(
                  REVERSE(
                          CONVERT(VARCHAR(50), value, 128)
                         ) AS float
                 ) AS bigint
            )
       ) as Decimals
    FROM Numbers
    

    Here's a SQL Fiddle to test this out: http://sqlfiddle.com/#!6/23d4f/29


    To account for that little quirk, here's a modified version that will handle the case when the float value has no decimal part:

    SELECT value,
           Decimals = CASE Charindex('.', value)
                        WHEN 0 THEN 0
                        ELSE
               Len (
                Cast(
                 Cast(
                  Reverse(CONVERT(VARCHAR(50), value, 128)) AS FLOAT
                     ) AS BIGINT
                    )
                   )
                        END
    FROM   numbers
    

    Here's the accompanying SQL Fiddle: http://sqlfiddle.com/#!6/10d54/11