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


Significant Digits


Significant Digits

Author
Message
DMinard
DMinard
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87334 Visits: 41113

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.
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
DMinard
DMinard
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87334 Visits: 41113

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