How many decimal places does that SQL float have?

On of the problems I seem to come across a lot of late, is the need to determine the precision of a number in T-SQL. This is partly to determine if one number is just a rounded version of another number, when for example the rounding is not to a known number of decimal places.

This is a somewhat ugly and convoluted problem to solve with T-SQL, so here is a solution to match:

CREATE FUNCTION [dbo].[decimalPrecision]

(

      -- Add the parameters for the function here

      @num float

)

RETURNS int

AS

BEGIN

      DECLARE @places int, @index int

      declare @str char(250)

     

      set @str = str(@num,250,16)

      set @index = charindex('.',@str);

     

      select @places = len(

            replace(

                  substring(

                        @str,

                        @index,

                        len(@str) - @index

                  ),

                  '0',

                  ' '

            )

      ) - 1

      RETURN @places

END

Basically, the aproach is to convert to a string using str, since convert would truncate to two decimal places, and then find the string that follows the decimal place. However, this would also include trailing zeros, so we replace zeros with a space. The len function will ignore trailing spaces, and so will provide the precision of the number. 

A more complete solution would probably be based on scientific notation, however again, the convert function would also trims precision.