Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Significant Digits Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, May 24, 2006 1:38 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, February 20, 2015 3:06 PM 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            -    1200000123456              -    12000012345                -    120001234                  -    1200123                    -    12012                      -    121                        -    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 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 allselect 12345678900 union allselect 1234567890 union allselect 123456789 union allselect 12345.6789 union allselect 1234.56789 union allselect 123.456789 union allselect 12.3456789 union allselect 1.23456789 union allselect 0.123456789 union allselect 0.0123456789 union allselect 0.00123456789 union allselect 0.000123456789 union allselect 0.0000123456789--declare @sigfigs tinyintselect @sigfigs = 0--while @sigfigs < 11begin--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_valuefrom @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 Group: General Forum Members Last Login: Friday, February 20, 2015 3:06 PM 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).DennisALTER FUNCTION [dbo].[2SignificantDigits] ( @Value decimal(18,9))RETURNS varchar(20) asBEGIN RETURN(SelectCase When Floor(@Value) = 0 Then -- First handle all the values that are less than zeroCase When Substring(Cast(@Value As varchar(20)),3,1) <> '0' ThenCaseWhen 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))EndElseCaseWhen Substring(Cast(@Value As varchar(20)),4,1) <> '0' ThenCast(Cast(Cast(Round(@Value,3) As varchar(20)) As decimal(18,3)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),5,1) <> '0' ThenCast(Cast(Cast(Round(@Value,4) As varchar(20)) As decimal(18,4)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),6,1) <> '0' ThenCast(Cast(Cast(Round(@Value,5) As varchar(20)) As decimal(18,5)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),7,1) <> '0' ThenCast(Cast(Cast(Round(@Value,6) As varchar(20)) As decimal(18,6)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),8,1) <> '0' ThenCast(Cast(Cast(Round(@Value,7) As varchar(20)) As decimal(18,7)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),9,1) <> '0' ThenCast(Cast(Cast(Round(@Value,8) As varchar(20)) As decimal(18,8)) As varchar(20))ElseCaseWhen Substring(Cast(@Value As varchar(20)),10,1) <> '0' ThenCast(Cast(Cast(Round(@Value,9) As varchar(20)) As decimal(18,9)) As varchar(20))Else '0.0'EndEndEndEndEndEndEndEndElse -- Now handle those values greater than zeroCase 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))EndEnd)END -- Function
Post #282888
 Posted Thursday, May 25, 2006 11:50 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:52 PM Points: 42,083, Visits: 39,479
 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 numberDECLARE @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 functionDECLARE @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 #MyHeadDon'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." Helpful Links:How to post code problemsHow to post performance problems
Post #282988
 Posted Friday, May 26, 2006 3:08 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, February 20, 2015 3:06 PM 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
Post #283260
 Posted Friday, May 26, 2006 8:46 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:52 PM Points: 42,083, Visits: 39,479
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #283275

 Permissions