phan phan - 11 months ago 54
SQL Question

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?

Answer Source

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