Solved

T-SQL Function to correctly display of n number of Significant Figures

Posted on 2011-03-23
19
930 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:ExpertUserId
  • 8
  • 7
  • 4
19 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
You can try like this. try changing the value of @pre to 3 or 4 or 5 and see how the result changes.
declare @var varchar(100),@pre int
select @var = '0.0123456789012345',@pre = 2
declare @sql nvarchar(100)
select @sql = 'select CONVERT(decimal(18,'+convert(varchar,@pre)+'),'''+@var+''')'
exec (@sql)

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I understand, working on the SQL
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think you want this function (dbo.fix)
http://sqlblogcasts.com/blogs/rob_farley/pages/829.aspx
0
 

Author Comment

by:ExpertUserId
Comment Utility
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Open in new window

0
 

Author Comment

by:ExpertUserId
Comment Utility
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

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
*/

Open in new window

0
 

Author Comment

by:ExpertUserId
Comment Utility
You can't just use the ROUND founction to accomplish 2 sig fig.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
>> You can't just use the ROUND founction to accomplish 2 sig fig.

Why? Do you have data breaking that?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>This change to fix did not work.
example?

>I have altered my Fix function.
how?
0
 

Author Comment

by:ExpertUserId
Comment Utility
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
0
 

Author Comment

by:ExpertUserId
Comment Utility
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!
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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

Open in new window

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
*/

Open in new window

Limitation: This function returns 2 sig figs only.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 ...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Open in new window

0
 

Author Comment

by:ExpertUserId
Comment Utility
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.  
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
found the issue for > 9.99
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 @x not like '.%' and @res not like '%.%')
  begin
    set @res = @res + '0'
    set @x =  substring(@x, 2, 1000)
  end

  return (@res)
end

Open in new window

0
 

Author Closing Comment

by:ExpertUserId
Comment Utility
Thank you for the help!
0
 

Author Comment

by:ExpertUserId
Comment Utility
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(log10(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,@nChars)

  RETURN (@res)

END


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now