## Count Decimal Places

 Author Message Keith Saynor Valued Member Group: General Forum Members Points: 58 Visits: 22 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 = 10What I really want is a count of the number of digits following the decimal point ignoring the trailing zerose.g. 0.8333 result should be 4 0.99 result should be 2 Any ideas - Thanks Ken McKelvey SSCrazy Eights Group: General Forum Members Points: 9521 Visits: 8932 DECLARE @D DECIMAL(18,9) ,@S VARCHAR(20) ,@R VARCHAR(20)SET @D = 0.8333SET @S = CAST(@D AS VARCHAR(20))SET @R = REVERSE(SUBSTRING(@S, CHARINDEX('.', @S) + 1, 20))SELECT LEN(SUBSTRING(@R, PATINDEX('%[1-9]%', @R), 20)) Ken McKelvey SSCrazy Eights Group: General Forum Members Points: 9521 Visits: 8932 Forgot about no Decimal places:DECLARE @D DECIMAL(18,9) ,@S VARCHAR(20) ,@R VARCHAR(20) ,@Pos SMALLINTSET @D = 0.0SET @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 0ELSE SELECT LEN(SUBSTRING(@R, @Pos, 20)) R Michael SSCrazy Group: General Forum Members Points: 2349 Visits: 275 For what it's worth, here is a set-based approach. It requires a numbers table though.`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] ENDGOCREATE FUNCTION [dbo].[CountDP] ( @decNumber DECIMAL(18, 9) )RETURNS TINYINTAS 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 ENDGO` SQL guy and Houston Magician Sergiy SSC Guru Group: General Forum Members Points: 59796 Visits: 12988 This function is about 100 times faster:IF EXISTS (SELECT * FROM sysobjects WHERE name = N'DecimalPlaces') DROP FUNCTION DecimalPlacesGOCREATE FUNCTION dbo.DecimalPlaces (@A float)RETURNS tinyintASBEGINdeclare @R tinyintIF @A IS NULLRETURN NULLset @R = 0while @A - str(@A, 18 + @R, @r) <> 0begin SET @R = @R + 1endRETURN @RENDGO Sergiy SSC Guru Group: General Forum Members Points: 59796 Visits: 12988 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. Jeff Moden SSC Guru Group: General Forum Members Points: 512484 Visits: 44320 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 ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs R Michael SSCrazy Group: General Forum Members Points: 2349 Visits: 275 Ouch! I guess next time I should get more information on how a solution might be used before posting a response!Interesting solutions all around, I thought. Very slick guys! SQL guy and Houston Magician Vladan SSChampion Group: General Forum Members Points: 11778 Visits: 764 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)))+1SELECT @places----------- 1 (1 row(s) affected)DECLARE @Places INT SELECT @Places = FLOOR(LOG10(REVERSE(ABS(99.0000)+1)))+1SELECT @places----------- -2 (1 row(s) affected) Jeff Moden SSC Guru Group: General Forum Members Points: 512484 Visits: 44320 There would certainly appear to be a fly in the ointment ... thanks for the catch and sorry for the mistake folks... I'll see if I can fix it... Maybe I meant "Old guys drool" --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs