Get minimum and maximum decimal scale value

Question

I have float datatype column in sql server and I need to get minimum decimal scale and maximum decimal scale .

Here is SQL Fiddle I am using

Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), value, 128)
                     ) AS FLOAT) AS BIGINT))

Which work great but when value is something in negative eg -0.061 it throw error Error converting data type varchar to float. So for alter I search and found that there is function name PARSENAME(@var,1) which is working perfect for negative numbers too but this function returns after decimal value and return type is nchar

Eg : If float value is -0.061 its will return 061 of return type nchar So i can not use len function to get its length.

SO how do I make this working either by using fiddle or by using above function.

Answer 1

Try using the ABS() function

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