Very cool, David... fast as all get out... but try this... obviously, we have to know exactly what the scale of the decimal places is to use it or we come up with the wrong answer...
DECLARE @TestNum DECIMAL(38,15)
SET @TestNum = 99.123456789012345
SELECT 10-PATINDEX('%[^0]%',REVERSE(RIGHT(CAST(@TestNum as varchar),9))+'1')
However... you gave me one heck of an idea... the following takes a bit more time (1,000,000 records in about 12 seconds)...
DECLARE @TestNum DECIMAL(38,15)
SET @TestNum = 90 --99.123456780000000 --0.123456780000000 --99.1 --90
SELECT CHARINDEX('.',REVERSE(@TestNum))
-PATINDEX('%[^0]%',REVERSE(@TestNum))
...the neat thing about it is that you don't need to know the precision or scale of the decimal column... it figures it out...
--Jeff Moden
Change is inevitable... Change for the better is not.