ExpertUserId
asked on
T-SQL Function to correctly display of n number of Significant Figures
I need a T-SQL Function to correctly display of n number of Significant Figures. All over the web we have people who think they know what "significant figures" are and they confuse if with decimal places. But I am talking about the most scientically accurate definition which is best explained as a function that would diplay a store value of 0.01 as 0.010; or a stored value of 0.0100 if Numeric(n,4) with two sig figs as: 0.010 . or a stored value of 0.005 displayed as 0.0050. Or 0.94 as 0.94. The decimal places will NOT be constant. Meaning you display two figures to the right starting with and including the value that is greater than zero. This is a question for a top SQL expert.
Thanks,
ExpertUser
Thanks,
ExpertUser
I understand, working on the SQL
I think you want this function (dbo.fix)
http://sqlblogcasts.com/blogs/rob_farley/pages/829.aspx
http://sqlblogcasts.com/blogs/rob_farley/pages/829.aspx
ASKER
Angellll, I have been impressed with your work over the years. But, this 2 significant figures issue is a tricky one. Mostly because it comes from a scietific background and not a financial background.
Your suggested solution doesn't work because when I provide it a float value of 0.0100 it returns 0.01 which is not 2 sig figs. I should return 0.0010. The decimal places will NOT be constant. Meaning you display two figures to the right starting with and including the value that is greater than zero. I work for a Laboratory regulated by the FDA and this is what my science users are tell me.
Your suggested solution doesn't work because when I provide it a float value of 0.0100 it returns 0.01 which is not 2 sig figs. I should return 0.0010. The decimal places will NOT be constant. Meaning you display two figures to the right starting with and including the value that is greater than zero. I work for a Laboratory regulated by the FDA and this is what my science users are tell me.
ok, let me put my version of the "fix" function:
alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits -1
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
ASKER
Thank you for your effort. This change to fix did not work.
I have altered my Fix function. I then ran the following:
select 0.0100 as 'Input', dbo.fix(0.0100,2) as 'Output'
Union
select 0.0050 as 'input', dbo.fix(0.0050,2) as 'Output'
The results:
Input Output
0.0100 0.0
0.0050 0.0
I have altered my Fix function. I then ran the following:
select 0.0100 as 'Input', dbo.fix(0.0100,2) as 'Output'
Union
select 0.0050 as 'input', dbo.fix(0.0050,2) as 'Output'
The results:
Input Output
0.0100 0.0
0.0050 0.0
Why don't you try like this?
declare @dig int
select @dig = 2
create table #table(Num float)
insert #table values (1.234),(0.0100),(0.005),(0.94)
select *,ROUND(Num,@dig) RoundedValue
from #table
drop table #table
/*
Num RoundedValue
1.234 1.23
0.01 0.01
0.005 0.01
0.94 0.94
*/
ASKER
You can't just use the ROUND founction to accomplish 2 sig fig.
>> You can't just use the ROUND founction to accomplish 2 sig fig.
Why? Do you have data breaking that?
Why? Do you have data breaking that?
>This change to fix did not work.
example?
>I have altered my Fix function.
how?
example?
>I have altered my Fix function.
how?
ASKER
AngelllI,
I implemented the change you gave me and the results were zeros?
in other words: I ran your fix: alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits -1
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
Try and run these two test values 0.01 and 0.005. The return as zeros.
These should return as 0.010 and 0.0050 respectively. (2 sig figs)
you
I implemented the change you gave me and the results were zeros?
in other words: I ran your fix: alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits -1
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
Try and run these two test values 0.01 and 0.005. The return as zeros.
These should return as 0.010 and 0.0050 respectively. (2 sig figs)
you
ASKER
Reply to Sharath_123:
Your code you submitted does not ruturn 2 significant figures by the scientific defition of the term two sig figs. 0.01 is not 2 sig figs. .05 is not 2 sig figs. Please read my initial question again.
Thanks!
Your code you submitted does not ruturn 2 significant figures by the scientific defition of the term two sig figs. 0.01 is not 2 sig figs. .05 is not 2 sig figs. Please read my initial question again.
Thanks!
Create a function like this.
CREATE FUNCTION dbo.fn_Fix
(@num FLOAT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @rn INT,
@str VARCHAR(100)
SELECT @rn = 0;
;WITH cte
AS (SELECT string,
SUBSTRING(string,n + 1,1) string_val,
n,
ROW_NUMBER()
OVER(ORDER BY n DESC) rn
FROM (SELECT SUBSTRING(CONVERT(VARCHAR(100),@num),3,LEN(CONVERT(VARCHAR(100),@num))) string) t1
CROSS JOIN (SELECT NUMBER
FROM MASTER..spt_values
WHERE TYPE = 'P') AS Numbers(n)
WHERE n < LEN(string))
SELECT @str = '0.' + c1.string + CASE
WHEN c2.string_val = 0 THEN '0'
ELSE ''
END
FROM cte c1
LEFT JOIN cte c2
ON c1.rn + 1 = c2.rn
WHERE c1.rn = 1
RETURN @str
END
And use this function in the code.declare @dig int
select @dig = 2
create table #table(Num float)
insert #table values (0.234),(0.0100),(0.005),(0.94)
select *,dbo.fn_fix(Num) RoundedValue
from #table
drop table #table
/*
Num RoundedValue
0.234 0.234
0.01 0.010
0.005 0.0050
0.94 0.94
*/
Limitation: This function returns 2 sig figs only.
Sharath_123
Num RoundedValue
0.234 0.234
is not requested, but:
Num RoundedValue
0.234 0.23
I will be looking in a couple of hours into my function ...
Num RoundedValue
0.234 0.234
is not requested, but:
Num RoundedValue
0.234 0.23
I will be looking in a couple of hours into my function ...
found my issue ...
alter function dbo.fix(@num float, @digits int) returns varchar(1000) as
begin
declare @x varchar(1000)
declare @res varchar(1000)
set @x = cast(isnull(cast(@num as decimal(28,14)),0) as varchar(1000))
if @x like '.%' set @x = '0' + @x
set @res = ''
while @digits > 0
begin
if @x like '.[0-9]%' set @res = @res + left(@x,2)
if @x like '[0-9]%' set @res = @res + left(@x,1)
if @x = '' and @res like '%.%' set @res = @res + '0'
if @x = '' and @res not like '%.%' set @res = @res + '.0'
set @digits = @digits - case when replace(replace(@res, '0', ''), '.', '') <> '' then 1 else 0 end
if @x <> ''
begin
if @x like '.%' set @x = substring(@x, 2, 1000)
set @x = substring(@x, 2, 1000)
end
end
while @x like '[0-9]%.%' or ( @x <> '' and @res not like '%.%')
begin
set @res = @res + '0'
set @x = substring(@x, 2, 1000)
end
return (@res)
end
ASKER
Angel,
Your new verson seems to work for values <= 9.99 but what happens when your value is 10 or greater?
For my purposes this may work, but I'm not sure. I need to do some research on this.
Your new verson seems to work for values <= 9.99 but what happens when your value is 10 or greater?
For my purposes this may work, but I'm not sure. I need to do some research on this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the help!
ASKER
The code that won this question has failed. If you are regulated by the FDA and your numbers are scientific in nature instead of financial, then you will need the following code:
/****** Object: UserDefinedFunction [dbo].[fn_sigfig] Script Date: 07/19/2011 11:37:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].fn_SigFig(@inValue DECIMAL(38,20), @inDecimalPlaces INT ) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @res VARCHAR(100)
DECLARE @fullNumber VARCHAR(100)
DECLARE @nChars INT
IF @inValue IS NULL BEGIN
SET @res = NULL
RETURN (@res)
END
-- rather than rounding, drop trailing zeros by casting the value to a float, then to a varchar
IF @inDecimalPlaces IS NULL BEGIN
SET @res = CAST(CAST(@inValue AS FLOAT) AS VARCHAR)
RETURN (@res)
END
SET @inDecimalPlaces = @inDecimalPlaces-1-floor(l og10(abs(@ inValue)))
SET @fullNumber = CAST(@inValue AS VARCHAR)
IF @inDecimalPlaces = 0 BEGIN
-- set it to negative 1 so that we drop the decimal point
SET @inDecimalPlaces = -1
END
SET @nChars = CHARINDEX('.',@fullNumber) + @inDecimalPlaces
SET @res = SUBSTRING(@fullNumber,1,@n Chars)
RETURN (@res)
END
/****** Object: UserDefinedFunction [dbo].[fn_sigfig] Script Date: 07/19/2011 11:37:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].fn_SigFig(@inValue DECIMAL(38,20), @inDecimalPlaces INT ) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @res VARCHAR(100)
DECLARE @fullNumber VARCHAR(100)
DECLARE @nChars INT
IF @inValue IS NULL BEGIN
SET @res = NULL
RETURN (@res)
END
-- rather than rounding, drop trailing zeros by casting the value to a float, then to a varchar
IF @inDecimalPlaces IS NULL BEGIN
SET @res = CAST(CAST(@inValue AS FLOAT) AS VARCHAR)
RETURN (@res)
END
SET @inDecimalPlaces = @inDecimalPlaces-1-floor(l
SET @fullNumber = CAST(@inValue AS VARCHAR)
IF @inDecimalPlaces = 0 BEGIN
-- set it to negative 1 so that we drop the decimal point
SET @inDecimalPlaces = -1
END
SET @nChars = CHARINDEX('.',@fullNumber)
SET @res = SUBSTRING(@fullNumber,1,@n
RETURN (@res)
END
Open in new window