Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Significant Digits Expand / Collapse
Author
Message
Posted Wednesday, May 24, 2006 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 11:21 AM
Points: 3, Visits: 37

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?



Post #282576
Posted Wednesday, May 24, 2006 4:45 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 PM
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
Post #282620
Posted Thursday, May 25, 2006 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 11:21 AM
Points: 3, Visits: 37

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




Post #282888
Posted Thursday, May 25, 2006 11:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #282988
Posted Friday, May 26, 2006 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 11:21 AM
Points: 3, Visits: 37

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




Post #283260
Posted Friday, May 26, 2006 8:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #283275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse