SQL function that returns significant figures as varchar

Question

I'm trying to modify a SQL function posted back in 2009. The function returns a float, but I need the trailing zeros as well. For example with 2 significant figures:

2153.012 -> "2200"
123.361 -> "120"
12.331 -> "12"
1.014 -> "1.0"
0.804 -> "0.80"
0.0011 -> "0.0011"
0.001 -> "0.0010"

EDIT: I should not have assumed people were familiar with significant figures (apologies). It's a simple way of distinguishing when 0s are place holders or values.

For numbers less than 1, the 0s immediately after the decimal are place holders, but trailing 0s are values. So, 0.0010 indicates that the zero after the 1 is a real value. With 3 sig figs, the number would need to be written as 0.00100.

For numbers larger than 1, it’s not always obvious without knowing the number of sig figs. For example, are the zeros in 2000 place holders or values?

EDIT2: Some more examples (upon request).

Large numbers

Measured	1 Sig Fig	2 Sig Figs	3 Sig Figs	4 Sig Figs	5 Sig Figs
80125.01255	80000	80000	80100	80130	80125
8012.01255	8000	8000	8010	8012	8012.0
801.012555	800	800	8.01	801.0	801.01
80.01255	80	80	80.0	80.01	80.013
8.01255	8	8.0	8.01	8.013	8.0126

Small numbers

Measured	1 Sig Fig	2 Sig Figs	3 Sig Figs	4 Sig Figs	5 Sig Figs
0.100362	0.1	0.10	0.100	0.1004	0.10036
0.010362	0.01	0.010	0.0104	0.01036	0.010362
0.001036	0.001	0.0010	0.00104	0.001036	0.0010360
0.000103	0.0001	0.00010	0.000103	0.0001030	0.00010300
0.000010	0.00001	0.000010	0.0000100	0.00001000	0.000010000
0.000001	0.000001	0.0000010	0.00000100	0.000001000	0.0000010000

The function I'm modifying is:

CREATE FUNCTION RoundSigFig(@Number float, @Figures int)
RETURNS float
AS
BEGIN

    DECLARE @Answer float;

    SET @Answer = (
    SELECT
        CASE WHEN intPower IS NULL THEN 0
        ELSE FLOOR(fltNumber * POWER(CAST(10 AS float), intPower) +0.5) 
            *POWER(CAST(10 AS float), -intPower)
        END AS ans
    FROM (
        SELECT
            @Number AS fltNumber,
            CASE WHEN @Number > 0
                THEN -((CEILING(LOG10(@Number)) -@Figures))
            WHEN @Number< 0
                THEN -((FLOOR(LOG10(@Number)) -@Figures))
            ELSE NULL END AS intPower       
        ) t
    );

    RETURN @Answer;
END

It works as advertised. To get the output as varchar, I've attempted the use the FORMAT function and a bunch of CASE expressions to pad out the needed zeros, but never managed to get it to work.

Please show what you tried. I would imagine you need `RETURNS varchar(20)` and something like `RETURN CONVERT(varchar(20), @Answer, 2);` or similar — Charlieface, May 21 '23 at 15:19
Please show use what you mean by "**need the trailing zeros as well**" e.g. `2153.012 -> "2200.00"` i.e. you want to add 2 zeros? and `0.0011 -> "0.001100"` ??? it's really not clear what you are asking for — Paul Maxwell, May 21 '23 at 23:44
Believe I have have found a solution. & I opted to add another answer so comments will be "in context". — Paul Maxwell, May 24 '23 at 04:48
I'm curious why you need to do that in SQL? Surely whatever app you consume this data with can resolve that for you? — Dale K, Jun 02 '23 at 04:33

Paul Maxwell · Accepted Answer · 2023-05-28T03:42:47.733

The question calls for a very specific style of numeric formatting, references for this are columbia.edu 1 and columbia.edu 2

The first part of answering was to verify the existing function (LimitSigFigs) from 2009 (link in the question). This does work but I believe there is a simpler equivalent (LimitSigFigsv1). I also believe that it is beneficial to retain a function that will return a float (e.g. for use in ordering results) so either of these functions may be used in the subsequent formating.

As it turns out both of the numeric functions do most of the "heavy lifting" and in the main all one needs to do is force that output into a string, which can be done via the format function (nb: I tried str() and it simply did not work as well as format does). However the special formatting specified requires some additional zeros to small numbers that require some special handling. This is done by inspecing the length of the string and the position of the first non-zero digit after the decimal point. This determines if additional zeros are required, how many are reqired, and then appends those if needed.

In the end (after many iterations) the formatting function is quite simple (which is nice!) and hopefully will perform reasnably well. Note, I opted to prepare a second function that "passes thru" the parameters into the existing numeric function for simplicity. Also note user defined scalar functions are not known for their performance - but they are convenient.

The following is a simplified function to round to a significant number, you may use this or the original, but note I haven't done very extensive testing with either.

CREATE FUNCTION [dbo].[LimitSigFigsV1](@value FLOAT, @sigFigs INT) RETURNS FLOAT AS
BEGIN
  DECLARE @result FLOAT
  IF (@sigFigs < 1)
    SET @result = NULL
  ELSE
    SET @result = CASE WHEN @value = 0 THEN 0 
                       ELSE ROUND(@value, @sigFigs - 1 - FLOOR(LOG10(ABS(@value)))) 
                  END
  RETURN @result
END

takes a floating point number value and an integer sigFigs as inputs.
returns a floating point number rounded to the specified number of significant figures.
If the sigFigs input is less than 1, return NULL.
If the value input is 0, return 0.
Otherwise, round the value to the specified number of significant figures using the ROUND function
then returns the rounded value.

Then the final formating is conducted through this function:

CREATE FUNCTION FormatSigFigsV2(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
BEGIN 
  DECLARE @Result VARCHAR(200); 
  SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################'); 

  /* Count the number of significant digits */
  DECLARE @SigFigs INT = 0;
  DECLARE @i INT = 1;
  WHILE @i <= LEN(@Result) AND @SigFigs < @Figures
  BEGIN
    IF SUBSTRING(@Result, @i, 1) BETWEEN '1' AND '9'
      SET @SigFigs = @SigFigs + 1;
    SET @i = @i + 1;
  END

  /* Add trailing zeros if necessary */
  IF CHARINDEX('.', @Result) > 0 AND @SigFigs < @Figures
    SET @Result = @Result + REPLICATE('0', @Figures - @SigFigs);
  ELSE IF CHARINDEX('.', @Result) = 0 AND @SigFigs < @Figures
    SET @Result = @Result + '.' + REPLICATE('0', @Figures - @SigFigs);

  RETURN @Result; 
END

first calls another function called dbo.LimitSigFigsv1 to limit the number of significant figures in the input number to the specified number of figures
then counts the number of significant digits in the result by iterating over each character in the string and checking if it is a non-zero digit
If the result contains a decimal point and the number of significant digits is less than the specified number of figures, adds trailing zeros after the decimal point
Or, if the result does not contain a decimal point and the number of significant digits is less than the specified number of figures, adds a decimal point followed by trailing zeros

Note limited testing has been undertaken, using the following table within the range of 1 to 5 for the significant number. (ps: I changed the inital test set from (say) 80125.01255 to 87125.112550 to reduce confusion with the number and placement of zeros).

The remainder of this answer replicates the details of a fiddle site, hopefully this will allow you to follow the tests conducted and how the functions are used.

CREATE TABLE measurements (
  value decimal(24,8)
);

INSERT INTO measurements (value)
VALUES
  (87125.112550),  (8712.112550),  (871.112550),  (87.112550),  (8.112550),
  (0.111362),  (0.011362),  (0.001136),  (0.000113),  (0.000011),  (0.000001)
  , (1.2), (1.4), (1.8564), (1.98765)
  ,(100000),(9000),(100),(90),(10),(9), (0);

22 rows affected

CREATE FUNCTION [dbo].[LimitSigFigsV1](@value FLOAT, @sigFigs INT) RETURNS FLOAT AS
BEGIN
  DECLARE @result FLOAT
  IF (@sigFigs < 1)
    SET @result = NULL
  ELSE
    SET @result = CASE WHEN @value = 0 THEN 0 
                       ELSE ROUND(@value, @sigFigs - 1 - FLOOR(LOG10(ABS(@value)))) 
                  END
  RETURN @result
END

/* while loop logic */
CREATE FUNCTION FormatSigFigsV2(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
BEGIN 
  DECLARE @Result VARCHAR(200); 
  SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################'); 

  /* Count the number of significant digits */
  DECLARE @SigFigs INT = 0;
  DECLARE @i INT = 1;
  WHILE @i <= LEN(@Result) AND @SigFigs < @Figures
  BEGIN
    IF SUBSTRING(@Result, @i, 1) BETWEEN '1' AND '9'
      SET @SigFigs = @SigFigs + 1;
    SET @i = @i + 1;
  END

  /* Add trailing zeros if necessary */
  IF CHARINDEX('.', @Result) > 0 AND @SigFigs < @Figures
    SET @Result = @Result + REPLICATE('0', @Figures - @SigFigs);
  ELSE IF CHARINDEX('.', @Result) = 0 AND @SigFigs < @Figures
    SET @Result = @Result + '.' + REPLICATE('0', @Figures - @SigFigs);

  RETURN @Result; 
END

SELECT 
    value AS Measured,
    dbo.FormatSigFigsV2(value, 1) AS Sig_1a,
    dbo.FormatSigFigsV2(value, 2) AS Sig_2a,
    dbo.FormatSigFigsV2(value, 3) AS Sig_3a,
    dbo.FormatSigFigsV2(value, 4) AS Sig_4a,
    dbo.FormatSigFigsV2(value, 5) AS Sig_5a
FROM measurements;

Measured	Sig_1a	Sig_2a	Sig_3a	Sig_4a	Sig_5a
87125.11255000	90000	87000	87100	87130	87125
8712.11255000	9000	8700	8710	8712	8712.1
871.11255000	900	870	871	871.1	871.11
87.11255000	90	87	87.1	87.11	87.113
8.11255000	8	8.1	8.11	8.113	8.1126
0.11136200	0.1	0.11	0.111	0.1114	0.11136
0.01136200	0.01	0.011	0.0114	0.01136	0.011362
0.00113600	0.001	0.0011	0.00114	0.001136	0.0011360
0.00011300	0.0001	0.00011	0.000113	0.0001130	0.00011300
0.00001100	0.00001	0.000011	0.0000110	0.00001100	0.000011000
0.00000100	0.000001	0.0000010	0.00000100	0.000001000	0.0000010000
1.20000000	1	1.2	1.20	1.200	1.2000
1.40000000	1	1.4	1.40	1.400	1.4000
1.85640000	2	1.9	1.86	1.856	1.8564
1.98765000	2	2.0	1.99	1.988	1.9876
100000.00000000	100000	100000.0	100000.00	100000.000	100000.0000
9000.00000000	9000	9000.0	9000.00	9000.000	9000.0000
100.00000000	100	100.0	100.00	100.000	100.0000
90.00000000	90	90.0	90.00	90.000	90.0000
10.00000000	10	10.0	10.00	10.000	10.0000
9.00000000	9	9.0	9.00	9.000	9.0000
0.00000000	0.0	0.00	0.000	0.0000	0.00000

/*
+---------------+-----------+------------+------------+-------------+--------------+
|   Measured    | 1 Sig Fig | 2 Sig Figs | 3 Sig Figs | 4 Sig Figs  |  5 Sig Figs  |
+---------------+-----------+------------+------------+-------------+--------------+
| 87125.01255   | 90000     | 87000      | 87100      | 87130       | 87125        |
| 8712.01255    | 9000      | 8700       | 8710       | 8712        | 8712.0       |
| 871.012555    | 900       | 870        | 871        | 871.0       | 871.01       |
| 87.01255      | 90        | 87         | 87.0       | 87.01       | 87.013       |
| 8.01255       | 8         | 8.0        | 8.01       | 8.013       | 8.0126       |
| 0.100362      | 0.1       | 0.10       | 0.100      | 0.1004      | 0.10036      |
| 0.010362      | 0.01      | 0.010      | 0.0103     | 0.01036     | 0.010362     |
| 0.001036      | 0.001     | 0.0010     | 0.00104    | 0.001036    | 0.0010360    |
| 0.000103      | 0.0001    | 0.00010    | 0.000103   | 0.0001030   | 0.00010300   |
| 0.000010      | 0.00001   | 0.000010   | 0.0000100  | 0.00001000  | 0.000010000  |
| 0.000001      | 0.000001  | 0.0000010  | 0.00000100 | 0.000001000 | 0.0000010000 |
+---------------+-----------+------------+------------+-------------+--------------+
*/

fiddle

I have amended the formatting function so that integers are given a decimal point when this is needed (when zeros are appended). & Test cases include 1000, 900, 100, 90, 10, 9 and zero (not sure if zero is handled correctly btw). — Paul Maxwell, May 25 '23 at 04:08
Sorry, been doing my actual job. Wow, looks great. Tested it on hundreds of numbers - all good, even zero. Amazing. One minor oddity is decimal numbers between 1-10 ( i.e. `1.2`), It is correct for 2 sf, but off by 1 zero for 3 sf, 4 sf, etc. That is `2sf: 1.2 --> 1.2 (ok)`, but `3sf: 1.2 --> 1.2 (?)`, `4sf: 1.2 --> 1.20 (?)`. Numbers <1 and > 10 work perfectly. Seriously, it's all good - you've done enough. I typically only use 2 sf and I'll simply ignore that oddity. — , May 26 '23 at 21:22
Are you using the most recent code? have a look at these results https://dbfiddle.uk/Ww5KruU5 is there any error in those results? If not, please ensure you use the most recnt function code nb: there are 2 variants, you can choose whichever you prefer (I prefer the one *without* the small while loop) — Paul Maxwell, May 27 '23 at 00:31
If you look at the fiddle output again for `1.2` for Sig_2 and Sig_3 , you'll notice you got the same result as me - both are `1.2`. — , May 28 '23 at 02:23
OK - so now I am going to use the former "alternative logic" because it is correct (I believe) for all test cases so far (**but I am still unsure about what zero should do**). This means I have **changed the function** - and there is only 1 variant now - take note. I have changed the answer to match the **new function**. If further questions arise, please verify you are now using the "while loop" based function. — Paul Maxwell, May 28 '23 at 03:45
I will give it another go - you're awesome (obviously). As for zero, it doesn't really matter (seriously). If you want, you could give it the same sf as passed to the function (i.e. 0.00 for 2 sf) or just 0. Zero is never reported as a numerical value, but as something like "not detected...yada, yada". — , May 28 '23 at 05:22
Hi Paul, I ran hundreds of numbers thru the functions. Perfect!. Fantastic, thank you.! — , May 28 '23 at 13:18
Fabulous! Thanks for the confirmation (and challenge, which I enjoyed). — Paul Maxwell, May 28 '23 at 17:12

SQL function that returns significant figures as varchar

1 Answers1