SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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