SQLServerCentral / SQL Server 7,2000 / T-SQL / Count Decimal Places / Latest Posts

RE: Count Decimal Places

dwain.c — Mon, 11 Feb 2013 02:21:11 GMT

Then again, there's always some schmo that's going to come along and try to beat it.[code="sql"] SELECT TestNum, Jeff=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) END ,Markus=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END ,Dwain=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) END ,DwainRedux=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(TestNum - CAST(FLOOR(TestNum) AS DECIMAL)) AS DECIMAL)) ENDFROM ( SELECT CAST(99 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(-99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(-99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111111111111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(0.1 AS DECIMAL(38,15))) Nums(TestNum)CREATE TABLE #BigNums (TestNum DECIMAL(38,15))INSERT INTO #BigNumsSELECT 1.* CHECKSUM(NEWID()) / POWER(10, ABS(CHECKSUM(NEWID())) % 10)FROM ( SELECT TOP 1000000 1 FROM sys.all_columns a, sys.all_columns b )Tally(n)DECLARE @Hold DECIMAL(38,15)PRINT 'Jeff'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Markus'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain Redux'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(TestNum - CAST(FLOOR(TestNum) AS DECIMAL)) AS DECIMAL)) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Jeff - Revised WHEN'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFDROP TABLE #BigNums[/code]Latest speed results:[code="plain"]Jeff SQL Server Execution Times: CPU time = 3338 ms, elapsed time = 3408 ms.Markus SQL Server Execution Times: CPU time = 4337 ms, elapsed time = 4369 ms.Dwain SQL Server Execution Times: CPU time = 2012 ms, elapsed time = 2071 ms.Dwain Redux SQL Server Execution Times: CPU time = 1888 ms, elapsed time = 1928 ms.Jeff - Revised WHEN SQL Server Execution Times: CPU time = 2293 ms, elapsed time = 2362 ms.[/code]It appears that most of the speed boost was a result of the revision to the WHEN clause.

RE: Count Decimal Places

dwain.c — Mon, 11 Feb 2013 01:13:51 GMT

I know I must be doing something wrong here so will somebody please check me?[code="sql"] SELECT TestNum, Jeff=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) END ,Markus=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END ,Dwain=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM ( SELECT CAST(99 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.11111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(99.1111111111111111 AS DECIMAL(38,15)) UNION ALL SELECT CAST(0.1 AS DECIMAL(38,15))) Nums(TestNum)CREATE TABLE #BigNums (TestNum DECIMAL(38,15))INSERT INTO #BigNumsSELECT 1.* CHECKSUM(NEWID()) / POWER(10, ABS(CHECKSUM(NEWID())) % 10)FROM ( SELECT TOP 1000000 1 FROM sys.all_columns a, sys.all_columns b )Tally(n)DECLARE @Hold DECIMAL(38,15)PRINT 'Jeff'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Markus'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0 THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 ENDFROM #BigNumsSET STATISTICS TIME OFFPRINT 'Dwain'SET STATISTICS TIME ON SELECT @Hold=CASE WHEN FLOOR(TestNum) = TestNum THEN 0 ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) ENDFROM #BigNumsSET STATISTICS TIME OFFDROP TABLE #BigNums[/code]I get these timing results which just can't possibly be right. :w00t:[code="plain"]Jeff SQL Server Execution Times: CPU time = 3339 ms, elapsed time = 3411 ms.Markus SQL Server Execution Times: CPU time = 4227 ms, elapsed time = 4291 ms.Dwain SQL Server Execution Times: CPU time = 2028 ms, elapsed time = 2036 ms.[/code][b]Edit:[/b] Fixed the Tally table I used to set up the test harness to be SQL 2000 compatible (I think).

RE: Count Decimal Places

Jeff Moden — Wed, 06 Feb 2013 12:59:28 GMT

[quote][b]Markus S. Gallagher (2/5/2013)[/b][hr]actually no, i posted this answer before seeing the second page on the forum where you already improved on your initial answer so you can just ignore my previous post.for some reason i don't seem to be able to delete my posts :([/quote]Ah! Understood. Thank you for the feedback. Just to explain my question... it wasn't in defense of what I posted. I challenge anyone and everyone to nearly any claims of performance where a test to support such a claim has not been posted with the claim. It's usually not meant to be personal. it's meant to prevent the development of myths as so many myths have been formed.Shifting gears, the rolks at RedGate made it so you can't delete posts because a whole lot of people were deleting their posts once they 1) had and answer to the post or 2) had bad mouthed just about everyone and needed to be held accountable in public for their actions. They (folks at RedGate) decided it was better to simply not be able to delete posts and to leave such a thing only up to official RedGate moderators (mostly Steve Jones).

RE: Count Decimal Places

Markus S. Gallagher — Tue, 05 Feb 2013 08:07:47 GMT

actually no, i posted this answer before seeing the second page on the forum where you already improved on your initial answer so you can just ignore my previous post.for some reason i don't seem to be able to delete my posts :(

RE: Count Decimal Places

Jeff Moden — Tue, 05 Feb 2013 07:52:00 GMT

[quote][b]Markus S. Gallagher (2/5/2013)[/b][hr]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[/quote]Slight improvement how? Is it faster?

RE: Count Decimal Places

Markus S. Gallagher — Tue, 05 Feb 2013 03:06:02 GMT

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

RE: Count Decimal Places

Jeff Moden — Thu, 26 Oct 2006 17:20:00 GMT

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

RE: Count Decimal Places

David Burrows — Thu, 26 Oct 2006 07:46:00 GMT

10-PATINDEX('%[^0]%',REVERSE(RIGHT(CAST(mynumber as varchar),9))+'1')

1,000,000 rows <=10 secs

RE: Count Decimal Places

Jeff Moden — Wed, 25 Oct 2006 18:57:00 GMT

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

RE: Count Decimal Places

Jeff Moden — Wed, 25 Oct 2006 17:41:00 GMT

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"

RE: Count Decimal Places

Vladan — Wed, 25 Oct 2006 01:41:00 GMT

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)

RE: Count Decimal Places

Robert Michael Cary — Tue, 24 Oct 2006 23:34:00 GMT

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!

RE: Count Decimal Places

Jeff Moden — Wed, 11 Oct 2006 01:51:00 GMT

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

RE: Count Decimal Places

Sergiy — Tue, 10 Oct 2006 19:18:00 GMT

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.

RE: Count Decimal Places

Sergiy — Tue, 10 Oct 2006 15:35:00 GMT

This function is about 100 times faster:

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'DecimalPlaces') DROP FUNCTION DecimalPlacesGO

CREATE FUNCTION dbo.DecimalPlaces (@A float)RETURNS tinyintASBEGINdeclare @R tinyint

IF @A IS NULLRETURN NULL

set @R = 0

while @A - str(@A, 18 + @R, @r) <> 0begin SET @R = @R + 1end

RETURN @RENDGO

RE: Count Decimal Places

Robert Michael Cary — Tue, 10 Oct 2006 10:17:00 GMT

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

RE: Count Decimal Places

Ken McKelvey — Tue, 10 Oct 2006 09:37:00 GMT

Forgot about no Decimal places:

DECLARE @D DECIMAL(18,9) ,@S VARCHAR(20) ,@R VARCHAR(20) ,@Pos SMALLINT

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

RE: Count Decimal Places

Ken McKelvey — Tue, 10 Oct 2006 09:25:00 GMT

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

Count Decimal Places

Keith Saynor — Tue, 10 Oct 2006 08:49:00 GMT

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