Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Count Decimal Places Expand / Collapse
Author
Message
Posted Tuesday, October 10, 2006 8:49 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 = 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 

 




Post #314390
Posted Tuesday, October 10, 2006 9:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 845, Visits: 5,452

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))

Post #314401
Posted Tuesday, October 10, 2006 9:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 845, Visits: 5,452

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))

Post #314408
Posted Tuesday, October 10, 2006 10:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, 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]
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



SQL guy and Houston Magician
Post #314423
Posted Tuesday, October 10, 2006 3:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 4,576, Visits: 8,342

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

Post #314486
Posted Tuesday, October 10, 2006 7:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 4,576, Visits: 8,342

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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 36,777, Visits: 31,234

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #314556
Posted Tuesday, October 24, 2006 11:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, 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
Post #317805
Posted Wednesday, October 25, 2006 1:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 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)))+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)




Post #317824
Posted Wednesday, October 25, 2006 5:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 36,777, Visits: 31,234
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #318155
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse