i'm trying to count the number of decimal places in a field.

e.g. mynumber decimal 9 (18,9)

Len(mynumber) result = 11

I've tried converting it to a string Len(STR(mynumber)) result = 10

What I really want is a count of the number of digits following the decimal point ignoring the trailing zeros

e.g. 0.8333 result should be 4

      0.99 result should be 2

Any ideas - Thanks 

DECLARE @D DECIMAL(18,9)
 ,@S VARCHAR(20)
 ,@R VARCHAR(20)

SET @D = 0.8333
SET @S = CAST(@D AS VARCHAR(20))
SET @R = REVERSE(SUBSTRING(@S, CHARINDEX('.', @S) + 1, 20))

SELECT LEN(SUBSTRING(@R, PATINDEX('%[1-9]%', @R), 20))

Forgot about no Decimal places:

DECLARE @D DECIMAL(18,9)
 ,@S VARCHAR(20)
 ,@R VARCHAR(20)
 ,@Pos SMALLINT

SET @D = 0.0
SET @S = CAST(@D AS VARCHAR(20))
SET @R = REVERSE(SUBSTRING(@S, CHARINDEX('.', @S) + 1, 20))
SET @Pos = PATINDEX('%[1-9]%' , @R)
IF @Pos = 0
 SELECT 0
ELSE
 SELECT LEN(SUBSTRING(@R, @Pos, 20))

IF EXISTS ( SELECT  1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[CountDP]')
                    AND xtype IN ( N'FN', N'IF', N'TF' ) ) 
    BEGIN
        DROP FUNCTION [dbo].[CountDP]
    END
GO

CREATE FUNCTION [dbo].[CountDP]
    (
      @decNumber DECIMAL(18, 9) 
    )
RETURNS TINYINT
AS BEGIN
/*******************************************************************************************************
*   dbo.CountDP
*
*   Usage:
        print dbo.countdp(10.0000001) -- 7
        print dbo.countdp(10) -- 0
        print dbo.countdp(10.000) -- 0
        print dbo.countdp(0) -- 0
        print dbo.countdp(0.1234567) -- 7 
        print dbo.countdp(null) -- null
        print dbo.countdp() --ERROR

*
*   Modifications:   
*   Developer Name      Date        Brief description
*   ------------------- ----------- ------------------------------------------------------------
*   
********************************************************************************************************/

    DECLARE @DecCount TINYINT

    SELECT  @DecCount = ( SELECT    ISNULL(MAX(num), N.Num)
                          FROM      Numbers
                          WHERE     Num < 18
                                    AND Num > N.Num
                                    AND SUBSTRING(CAST(@decNumber AS VARCHAR),
                                                  Num, 1) NOT IN ( '0', '' )
                         ) - ( Num )
    FROM    Numbers N
    WHERE   Num < LEN(CAST(@decNumber AS VARCHAR(18)))
            AND SUBSTRING(CAST(@decNumber AS VARCHAR), Num, 1) = '.'
    RETURN @DecCount

   END
GO

This function is about 100 times faster:

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'DecimalPlaces')
 DROP FUNCTION DecimalPlaces
GO

CREATE FUNCTION dbo.DecimalPlaces
 (@A float)
RETURNS tinyint
AS
BEGIN
declare @R tinyint

IF @A IS NULL
RETURN NULL

set @R = 0

while @A - str(@A, 18 + @R, @r)  <> 0
begin
 SET @R = @R + 1
end

RETURN @R
END
GO

No, it's actually much worse.

I tested my function against 16540 row table.
It returned result in 3..5 seconds (I'm not alone on that server )

Than I started same query but using function [dbo].[CountDP].
I's been 2 hours 50 minutes since then, it's still going.

So, there is a reminder: avoid referencing tables inside UDF!
Even if it's such "set based" table as Numbers.

Old guys rule...   1 million conversions... 11 seconds... works for positive numbers, negative numbers, zero, and NULL...

DECLARE @Places INT
 SELECT TOP 1000000 @Places = FLOOR(LOG10(REVERSE(ABS(SomeNumber)+1)))+1
   FROM dbo.BigTest

Jeff,

are you sure the code you posted works? I tried it out of curiosity, and I'm getting some strange results... e.g. for number 99, depending on how I enter it, result is either 1 or -2.

DECLARE @Places INT
 SELECT @Places = FLOOR(LOG10(REVERSE(ABS(cast (99 as float))+1)))+1
SELECT @places
-----------
          1

(1 row(s) affected)

DECLARE @Places INT
 SELECT @Places = FLOOR(LOG10(REVERSE(ABS(99.0000)+1)))+1
SELECT @places
-----------
         -2

(1 row(s) affected)