Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Significant Digits


Significant Digits

Author
Message
DMinard
DMinard
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 38

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?




stax68
stax68
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 43

Here's one way:

declare @input table(fl float)
--
insert @input(fl)
select 123456789000 union all
select 12345678900 union all
select 1234567890 union all
select 123456789 union all
select 12345.6789 union all
select 1234.56789 union all
select 123.456789 union all
select 12.3456789 union all
select 1.23456789 union all
select 0.123456789 union all
select 0.0123456789 union all
select 0.00123456789 union all
select 0.000123456789 union all
select 0.0000123456789
--
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
DMinard
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 38

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

End

End

End

End

End

End

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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44974 Visits: 39866

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DMinard
DMinard
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 38

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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44974 Visits: 39866

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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