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)
  ,(1000),(900),(100),(90),(10),(9), (0);
18 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

    CREATE FUNCTION FormatSigFigsV1(@Number FLOAT, @Figures INT) RETURNS VARCHAR(200) AS 
    BEGIN 
      DECLARE @Result VARCHAR(200); 
      DECLARE @Len TINYINT;
      DECLARE @Pos1 TINYINT;
      DECLARE @Dot TINYINT;
      
      SET @Result = FORMAT(dbo.LimitSigFigsv1(@Number, @Figures),'0.################') ; 
      SET @Len    = LEN(@Result) ;
      SET @Pos1   = PATINDEX('%[1-9]%', (@Result + '9')) - 1 ; 
      SET @Dot    = CHARINDEX('.', @Result) ;

      /* if number is decimal but length < @figures add zeros */
      IF (@Len - @Pos1) < @Figures AND @Dot > 0
      BEGIN 
        SET @Result = @Result + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
      END 
      
      /* if number is integer but length < @figures add dot & zeros */
      IF (@Len - @Pos1) < @Figures AND @Dot = 0
      BEGIN 
        SET @Result = @Result + '.' + REPLICATE('0', @Figures - (@Len - @Pos1)) ; 
      END 
      
      RETURN @Result; 
    END
/* alternate 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.FormatSigFigsV1(value, 1) AS Sig_1,
    dbo.FormatSigFigsV1(value, 2) AS Sig_2,
    dbo.FormatSigFigsV1(value, 3) AS Sig_3,
    dbo.FormatSigFigsV1(value, 4) AS Sig_4,
    dbo.FormatSigFigsV1(value, 5) AS Sig_5,
    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_1
Sig_2
Sig_3
Sig_4
Sig_5
Sig_1a
Sig_2a
Sig_3a
Sig_4a
Sig_5a


87125.11255000
90000
87000
87100
87130
87125
90000
87000
87100
87130
87125

8712.11255000
9000
8700
8710
8712
8712.1
9000
8700
8710
8712
8712.1

871.11255000
900
870
871
871.1
871.11
900
870
871
871.1
871.11

87.11255000
90
87
87.1
87.11
87.113
90
87
87.1
87.11
87.113

8.11255000
8
8.1
8.11
8.113
8.1126
8
8.1
8.11
8.113
8.1126

0.11136200
0.1
0.11
0.111
0.1114
0.11136
0.1
0.11
0.111
0.1114
0.11136

0.01136200
0.01
0.011
0.0114
0.01136
0.011362
0.01
0.011
0.0114
0.01136
0.011362

0.00113600
0.001
0.0011
0.00114
0.001136
0.0011360
0.001
0.0011
0.00114
0.001136
0.0011360

0.00011300
0.0001
0.00011
0.000113
0.0001130
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.00001
0.000011
0.0000110
0.00001100
0.000011000

0.00000100
0.000001
0.0000010
0.00000100
0.000001000
0.0000010000
0.000001
0.0000010
0.00000100
0.000001000
0.0000010000

1000.00000000
1000
1000
1000
1000
1000.0
1000
1000.0
1000.00
1000.000
1000.0000

900.00000000
900
900
900
900.0
900.00
900
900.0
900.00
900.000
900.0000

100.00000000
100
100
100
100.0
100.00
100
100.0
100.00
100.000
100.0000

90.00000000
90
90
90.0
90.00
90.000
90
90.0
90.00
90.000
90.0000

10.00000000
10
10
10.0
10.00
10.000
10
10.0
10.00
10.000
10.0000

9.00000000
9
9.0
9.00
9.000
9.0000
9
9.0
9.00
9.000
9.0000

0.00000000
0.0
0.00
0.000
0.0000
0.00000
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_1	Sig_2	Sig_3	Sig_4	Sig_5	Sig_1a	Sig_2a	Sig_3a	Sig_4a	Sig_5a
87125.11255000	90000	87000	87100	87130	87125	90000	87000	87100	87130	87125
8712.11255000	9000	8700	8710	8712	8712.1	9000	8700	8710	8712	8712.1
871.11255000	900	870	871	871.1	871.11	900	870	871	871.1	871.11
87.11255000	90	87	87.1	87.11	87.113	90	87	87.1	87.11	87.113
8.11255000	8	8.1	8.11	8.113	8.1126	8	8.1	8.11	8.113	8.1126
0.11136200	0.1	0.11	0.111	0.1114	0.11136	0.1	0.11	0.111	0.1114	0.11136
0.01136200	0.01	0.011	0.0114	0.01136	0.011362	0.01	0.011	0.0114	0.01136	0.011362
0.00113600	0.001	0.0011	0.00114	0.001136	0.0011360	0.001	0.0011	0.00114	0.001136	0.0011360
0.00011300	0.0001	0.00011	0.000113	0.0001130	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.00001	0.000011	0.0000110	0.00001100	0.000011000
0.00000100	0.000001	0.0000010	0.00000100	0.000001000	0.0000010000	0.000001	0.0000010	0.00000100	0.000001000	0.0000010000
1000.00000000	1000	1000	1000	1000	1000.0	1000	1000.0	1000.00	1000.000	1000.0000
900.00000000	900	900	900	900.0	900.00	900	900.0	900.00	900.000	900.0000
100.00000000	100	100	100	100.0	100.00	100	100.0	100.00	100.000	100.0000
90.00000000	90	90	90.0	90.00	90.000	90	90.0	90.00	90.000	90.0000
10.00000000	10	10	10.0	10.00	10.000	10	10.0	10.00	10.000	10.0000
9.00000000	9	9.0	9.00	9.000	9.0000	9	9.0	9.00	9.000	9.0000
0.00000000	0.0	0.00	0.000	0.0000	0.00000	0.0	0.00	0.000	0.0000	0.00000