Count Decimal Places
-
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
-
Forgot about no Decimal places:
DECLARE @D DECIMAL(18,9)
,@S VARCHAR(20)
,@R VARCHAR(20)
,@Pos SMALLINT
SET @D = 0.0
SET @s-2 = CAST(@D AS VARCHAR(20))
SET @r = REVERSE(SUBSTRING(@S, CHARINDEX('.', @s-2) + 1, 20))
SET @Pos = PATINDEX('%[1-9]%' , @r)
IF @Pos = 0
SELECT 0
ELSE
SELECT LEN(SUBSTRING(@R, @Pos, 20))
-
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 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 GOSQL guy and Houston Magician
-
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
end
RETURN @r
END
GO
_____________
Code for TallyGenerator -
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.
_____________
Code for TallyGenerator -
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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)
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... this takes 2 seconds longer (1,000,000 rows in 23 seconds instead of 21)... had to work around the "zero domain" on the LOG10 function to get this to work properly for whole numbers...
DECLARE @TestNum DECIMAL(38,15)
SET @TestNum = 99 --99.0000
SELECT CASE
WHEN FLOOR(REVERSE(ABS(@TestNum))) = 0.0
THEN 0
ELSE FLOOR(LOG10(REVERSE(ABS(@TestNum)))+1)
END
It does NOT support FLOAT... (the original problem description was based on the DECIMAL datatype so I think we're ok there)... Float does wierd things when you throw a REVERSE on it. If anyone needs a decimal place counter that works on FLOAT, we'll have to take a different tact...
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
10-PATINDEX('%[^0]%',REVERSE(RIGHT(CAST(mynumber as varchar),9))+'1')
1,000,000 rows <=10 secs
Far away is close at hand in the images of elsewhere.
Anon. -
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
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Slight improvement that works for decimal datatype:
CASE WHEN FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 END
-
Markus S. Gallagher (2/5/2013)
Slight improvement that works for decimal datatype:CASE WHEN FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(Wert)+1)))+1 END
Slight improvement how? Is it faster?
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply