SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count Decimal Places


Count Decimal Places

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88012 Visits: 41128

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.
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 problems
How to post performance problems
Forum FAQs
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9717 Visits: 9764

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.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88012 Visits: 41128

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.
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 problems
How to post performance problems
Forum FAQs
Markus S. Gallagher
Markus S. Gallagher
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 39
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88012 Visits: 41128
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.
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 problems
How to post performance problems
Forum FAQs
Markus S. Gallagher
Markus S. Gallagher
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 39
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 Sad
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88012 Visits: 41128
Markus S. Gallagher (2/5/2013)
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 Sad


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

--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.
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 problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
I know I must be doing something wrong here so will somebody please check me?


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
FROM (
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 #BigNums
SELECT 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)
END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT '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 END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT '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))) END
FROM #BigNums
SET STATISTICS TIME OFF

DROP TABLE #BigNums



I get these timing results which just can't possibly be right. w00t


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.



Edit: Fixed the Tally table I used to set up the test harness to be SQL 2000 compatible (I think).


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
Then again, there's always some schmo that's going to come along and try to beat it.


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)) END
FROM (
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 #BigNums
SELECT 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)
END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT '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 END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT '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))) END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT '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)) END
FROM #BigNums
SET STATISTICS TIME OFF

PRINT 'Jeff - Revised WHEN'
SET STATISTICS TIME ON
SELECT @Hold=CASE
WHEN FLOOR(TestNum) = TestNum
THEN 0
ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1)
END
FROM #BigNums
SET STATISTICS TIME OFF

DROP TABLE #BigNums




Latest speed results:


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.



It appears that most of the speed boost was a result of the revision to the WHEN clause.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search