Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Count Decimal Places Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, October 10, 2006 8:49 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, March 29, 2011 6:07 AM Points: 4, Visits: 18
 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
Post #314390
 Posted Tuesday, October 10, 2006 9:25 AM
 SSC Eights! Group: General Forum Members Last Login: Yesterday @ 9:39 AM Points: 828, Visits: 5,199
 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))
Post #314401
 Posted Tuesday, October 10, 2006 9:37 AM
 SSC Eights! Group: General Forum Members Last Login: Yesterday @ 9:39 AM Points: 828, Visits: 5,199
 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))
Post #314408
 Posted Tuesday, October 10, 2006 10:17 AM
 SSC Veteran Group: General Forum Members Last Login: Friday, June 14, 2013 11:00 PM Points: 263, Visits: 274
 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
Post #314423
 Posted Tuesday, October 10, 2006 3:35 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 5:23 AM Points: 4,570, Visits: 8,297
 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
Post #314486
 Posted Tuesday, October 10, 2006 7:18 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 5:23 AM Points: 4,570, Visits: 8,297
 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.
Post #314507
 Posted Wednesday, October 11, 2006 1:51 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:21 PM Points: 34,537, Visits: 28,701
 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 Moden"RBAR 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #314556
 Posted Tuesday, October 24, 2006 11:34 PM
 SSC Veteran Group: General Forum Members Last Login: Friday, June 14, 2013 11:00 PM Points: 263, Visits: 274
 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
Post #317805
 Posted Wednesday, October 25, 2006 1:41 AM
 SSCommitted Group: General Forum Members Last Login: Thursday, May 05, 2011 1:38 AM Points: 1,636, Visits: 604
 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)
Post #317824
 Posted Wednesday, October 25, 2006 5:41 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:21 PM Points: 34,537, Visits: 28,701
 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 Moden"RBAR 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #318155

 Permissions