Significant Digits

Question

Post reply

Significant Digits

DMinard

Old Hand

Points: 335
More actions
May 24, 2006 at 1:38 pm

#190701

I'm trying to create a function dealing with "Significant Digits" for use in an application for our Statistics guys. I found out quickly that the term significant digits has different meanings for us computer geeks and stats geeks. I'm trying to output "2 significant digits". Zeros to the left don't count, but zeros to the right may count (it depends). A brief example of what I mean:
1234567            -    1200000
123456              -    120000
12345                -    12000
1234                  -    1200
123                    -    120
12                      -    12
1                        -    1.0
.1                       -    0.10
.01                     -    0.010
.001                   -    0.0010
.0001                 -    0.00010
.00001              -    0.000010
.000001            -    0.0000010
.12345              -    0.12
.012345            -    0.012
.001234            -    0.0012
.127                  -    0.13
.0127               -    0.013

I have a SQL statement with nested Cases that handle all but those small numbers. As you can see, if the small number only has a single non-zero digit, you must pad a zero on the right. Of course I have been converting to varchar to retain the ending zero.

My goal was to come up with a UDF that takes in the number, and number of significant digits requested, but if all I can come up with is a hard-coded 2-significant digits function, that will meet my immediate needs.

Anyone got any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

Here's one way:

--
insert
select
select
select
select
select
select
select
select
select
select
select
select
select
select

--

declare

@sigfigs tinyint

select

@sigfigs = 0

--

while

@sigfigs < 11

begin

--

select

fl float_value, @sigfigs sigfigs

,

floor(fl/power(cast(10 as float),floor(log10(fl))+1-@sigfigs))

*

power(cast(10 as float),floor(log10(fl))+1-@sigfigs) new_value

from

@input

--

select @sigfigs = @sigfigs + 1

--

end

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

DMinard Old Hand Points: 335 More actions · Answer 2

Tim,

This is very close to what I need, but using floats runs into the same problem I've been having with my attempts. Your method, like my previous attempts, would handle all but those small numbers (less than zero) in which there is only a single non-zero digit. Numbers like .1, .01, .001, etc. should come out .10, .010, .0010, etc. having them in float drops the zero.

Although it is hard-coded to only handle 2 significant digits, and would have to be modified to handle a different number of sig digits, below is what I came up with so far (forgive the fact that the case statement blocking gets deleted when I paste the statement in here).

Dennis

ALTER

FUNCTION [dbo].[2SignificantDigits] ( @Value decimal(18,9))

RETURNS varchar

(20) as

BEGIN

RETURN(

Select

Case When Floor(@Value) = 0 Then -- First handle all the values that are less than zero

Case

When Substring(Cast(@Value As varchar(20)),3,1) <> '0' Then

Case

When Substring(Cast(@Value As varchar(20)),4,1) = '0' Then

Cast(Cast(Cast(Round(@Value,2) As varchar(20)) + '0' As decimal(18,2)) As varchar(20))

When Substring(Cast(@Value As varchar(20)),4,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,2) As varchar(20)) As decimal(18,2)) As varchar(20))

End

Else

Case

When Substring(Cast(@Value As varchar(20)),4,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,3) As varchar(20)) As decimal(18,3)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),5,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,4) As varchar(20)) As decimal(18,4)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),6,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,5) As varchar(20)) As decimal(18,5)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),7,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,6) As varchar(20)) As decimal(18,6)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),8,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,7) As varchar(20)) As decimal(18,7)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),9,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,8) As varchar(20)) As decimal(18,8)) As varchar(20))

Else

Case

When Substring(Cast(@Value As varchar(20)),10,1) <> '0' Then

Cast(Cast(Cast(Round(@Value,9) As varchar(20)) As decimal(18,9)) As varchar(20))

Else '0.0'

End

Else -- Now handle those values greater than zero

Case CharIndex('.',@Value)

When 2 Then Cast(Cast(Cast(Round(@Value,1) As varchar(20)) As decimal(18,1)) As varchar(20))

When 3 Then Cast(Cast(Cast(Round(@Value,0) As varchar(20)) As decimal(18,0)) As varchar(20))

When 4 Then Cast(Cast(Cast(Round(@Value,-1) As varchar(20)) As decimal(18,0)) As varchar(20))

When 5 Then Cast(Cast(Cast(Round(@Value,-2) As varchar(20)) As decimal(18,0)) As varchar(20))

When 6 Then Cast(Cast(Cast(Round(@Value,-3) As varchar(20)) As decimal(18,0)) As varchar(20))

When 7 Then Cast(Cast(Cast(Round(@Value,-4) As varchar(20)) As decimal(18,0)) As varchar(20))

When 8 Then Cast(Cast(Cast(Round(@Value,-5) As varchar(20)) As decimal(18,0)) As varchar(20))

End

End)

END

-- Function

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

2nd post in as many days for this same thing... you guys got a little homework from the same professor?

Try this... most of it is test setup code... AND, it's programmable... AND, it could be turned into a function...

--===== Create a variable to hold the base test number

DECLARE @TestDigits DECIMAL(38,19)

SET @TestDigits = 1.245600085

--===== Create a variable to hold the number of significant digits to round to

-- This is NOT test setup code and would need to be included in a function

DECLARE @SigDigits TINYINT

SET @SigDigits = 3 --LOOK! Change this to vary the number of significant digits

--===== If the test table exists, drop it

IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL

DROP TABLE #MyHead

--===== Create the test table

CREATE TABLE #MyHead

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

TestValue DECIMAL(38,19)

)

--===== Populate the test table with values based on the base test value

INSERT INTO #MyHead (TestValue)

SELECT @TestDigits*1000000000. UNION ALL

SELECT @TestDigits*100000000.0 UNION ALL

SELECT @TestDigits*10000000.00 UNION ALL

SELECT @TestDigits*1000000.000 UNION ALL

SELECT @TestDigits*100000.0000 UNION ALL

SELECT @TestDigits*10000.00000 UNION ALL

SELECT @TestDigits*1000.000000 UNION ALL

SELECT @TestDigits*100.0000000 UNION ALL

SELECT @TestDigits*10.00000000 UNION ALL

SELECT @TestDigits*1.000000000 UNION ALL

SELECT @TestDigits*.1000000000 UNION ALL

SELECT @TestDigits*.0100000000 UNION ALL

SELECT @TestDigits*.0010000000 UNION ALL

SELECT @TestDigits*.0001000000 UNION ALL

SELECT @TestDigits*.0000100000 UNION ALL

SELECT @TestDigits*.0000010000 UNION ALL

SELECT @TestDigits*.0000001000 UNION ALL

SELECT @TestDigits*.0000000100 UNION ALL

SELECT @TestDigits*.0000000010 UNION ALL

SELECT @TestDigits*.0000000001

--===== Display the values rounded to @SigDigits significant digits

-- This is NOT test setup code and would need to be included in a function

SELECT TestValue,

STR(ROUND(TestValue, @SigDigits-1-FLOOR(LOG10(TestValue))),

38,CAST(

CASE

WHEN @SigDigits-1-FLOOR(LOG10(TestValue)) < 0

THEN 0

ELSE @SigDigits-1-FLOOR(LOG10(TestValue))

END

AS INT)

) AS Rounded

FROM #MyHead

Don't change DECIMAL(38,19) to REAL or FLOAT because you will get some pretty unpredictable results including the occasional scientific notation return.

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

DMinard Old Hand Points: 335 More actions · Answer 4

Jeff,

Thanks!! Just what I was looking for. Much cleaner (and shorter) than mine and has the flexibility that I was needing. I have converted it into a function and it works great.

Dennis

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Thanks for the feedback, Dennis. Appreciate it.

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

windchaser Newbie Points: 1 More actions · Answer 6

Thanks for the great solution Jeff Moden. I know it is almost 20 years later but still very usefull.