SQL function that returns significant figures as varchar

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

Small numbers

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.

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